The Relational Model
Tables, rows, columns, keys, and the mathematical foundation of relational databases.
What you'll learn
- How data is organized into tables, rows, and columns
- What primary keys and foreign keys are and why they matter
- The difference between a schema and a table
- How the relational model connects tables through relationships
Concept
Tables, Rows, and Columns
The relational model, introduced by Edgar F. Codd in 1970, organizes data into tables (also called relations). Each table has a set of named columns (attributes) that define what kind of data is stored, and each row (tuple) is a single record. For example, a users table might have columns id, name, and email, and each row represents one user.
Primary Keys
A primary key is a column (or combination of columns) that uniquely identifies each row in a table. No two rows can share the same primary key value, and it can never be NULL. Common choices are an auto-incrementing integer (id) or a UUID. The primary key is the anchor that lets you refer to a specific row from anywhere else.
Foreign Keys and Relationships
A foreign key is a column in one table that references the primary key of another table. This is how the relational model expresses relationships. For example, an orders table might have a user_id column that references users.id. The database enforces this link — you cannot insert an order with a user_id that does not exist in the users table.
Schema
A schema is the blueprint of your database: it defines every table, every column (with its data type), every constraint, and every relationship. The schema is fixed at design time (though it can be changed with migrations). This rigidity is a feature — it guarantees that every row conforms to the expected shape, preventing a whole class of data-quality bugs.
Example
-- users table with a primary key
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL
);
-- orders table with a foreign key to users
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id),
total DECIMAL(10, 2) NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
SERIAL auto-generates integer IDs. REFERENCES users(id) creates a foreign key — every order must belong to a real user.
Try it
- SQL Formatter
Format CREATE TABLE statements for readability.
- JSON Schema Validator
Schemas exist in JSON too — validate data against a schema.
Common mistakes
The mistake
Forgetting to define a primary key
The fix
Every table should have a primary key. Without one, you cannot reliably identify or update individual rows.
The mistake
Storing relationships as comma-separated values
The fix
Use a foreign key column or a junction table instead. CSV in a single column breaks joins and integrity.
Practice The Relational Model
2 exercises
Look up unfamiliar terms
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
Build real projects
Apply what you learned by building guided projects with starter code and solutions.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.