Databases and Data Modeling
Persisting structured data
Why databases exist
Databases store data reliably, support concurrent access, and make it possible to query information efficiently. Relational databases organize data into tables with rows and columns, while SQL provides a standard way to create, read, update, and delete records.
Relational database basics
Example schema ideas
| Entity | Possible columns | Notes |
|---|---|---|
| Users | id, name, email | Primary key on id |
| Orders | id, user_id, total | Foreign key user_id references Users |
| Products | id, name, price | Can be joined with orders |
| OrderItems | order_id, product_id, quantity | Represents many-to-many relationships |
What is the main purpose of a primary key?
A primary key uniquely identifies records in a table.
Correct answer: To uniquely identify each row
Why are indexes useful in databases?
Indexes trade extra storage and update cost for faster reads.
Correct answer: They speed up searching and joining by reducing the amount of data the database must scan.
Query thinking
SQL queries describe what data you want, not necessarily how to get it. The database optimizer chooses an execution plan. Understanding joins, filters, grouping, and aggregation helps you write better queries and reason about performance.
Which SQL concept combines rows from related tables?
Joins connect rows based on related key values.
Correct answer: Join
What is normalization trying to reduce?
Normalization organizes data to improve consistency.
Correct answer: Redundant data and update anomalies.
Data design trade-off
Highly normalized designs reduce duplication, but sometimes denormalization is used to improve query speed or simplify analytics.