The Relational Model
A relational database organizes data into tables. Each table is a collection of rows that share the same shape, defined by its columns. This model, invented by E. F. Codd in 1970, still powers most production systems today.
Tables, Rows, and Columns
A table represents one kind of thing — users, orders, products. Each row is a single instance of that thing, and each column is one attribute, like name or email. Every column has a fixed data type (integer, text, date) so the database can store and validate data efficiently.
Primary Keys
A primary key uniquely identifies each row in a table. Most tables use an auto-incrementing integer id column. The database guarantees no two rows share the same primary key, so you can always look up exactly one record by its key.
Foreign Keys
A foreign key is a column in one table that points to the primary key of another table. In the code example, orders.user_id references users.id. This is how relational data stays connected — instead of duplicating user info inside every order, each order just stores a reference.
Normalization
Normalization is the practice of splitting data into multiple tables to avoid duplication. If three orders all belonged to "Ada," storing her name and email three times wastes space and creates update anomalies. Putting users in their own table and linking by user_id fixes this. The first three "normal forms" cover the most common cases: each column holds one value, every non-key column depends on the key, and dependencies between non-key columns are eliminated.
Try It Yourself
- Add a
productstable with columnsid,name, andprice, then create anorder_itemstable that links orders to products - Find all orders for a given user without using a loop inside a loop (hint: build a lookup map first)
- Sketch a schema for a library: books, authors, and borrowers