Relational Model — Tables, Keys, Relationships
In simple words
In a relational database, data is kept in tables — like spreadsheets with rows and columns. Every row gets a unique id, called its primary key. To link two tables, one table stores the other's id in a column, called a foreign key — for example an orders table holds the user_id of the person who placed each order. The database then checks that every link points at a real row, so you can't have an order for a user who doesn't exist.
What is the Relational Model?
The relational model (introduced by Edgar F. Codd, 1970) organises data into relations (tables) where:
- Every table has a fixed set of named columns (attributes)
- Every row (tuple) represents one record
- Every cell holds one atomic value
- Relationships between tables are expressed through keys
The word "relational" comes from the mathematical concept of a "relation"
(a set of tuples) — not from "relationships between tables."
But in practice, the model is powerful because it lets you relate tables
to each other using keys.
Tables — The Building Block
A table (relation) has:
- Schema — the structure (column names + data types) — defined once
- Rows (tuples/records) — the actual data — added over time
- Columns (attributes/fields) — the properties each row has
Table: users
┌─────────┬───────────┬──────────────────────┬──────┬────────────┐
│ user_id │ name │ email │ age │ created_at │
├─────────┼───────────┼──────────────────────┼──────┼────────────┤
│ 1 │ Alice │ alice@email.com │ 28 │ 2024-01-10 │
│ 2 │ Bob │ bob@email.com │ 34 │ 2024-01-11 │
│ 3 │ Carol │ carol@email.com │ 25 │ 2024-01-12 │
└─────────┴───────────┴──────────────────────┴──────┴────────────┘
Schema: user_id (INT), name (VARCHAR), email (VARCHAR), age (INT), created_at (DATE)
Row: 1 | Alice | alice@email.com | 28 | 2024-01-10
Column: email → alice@email.com, bob@email.com, carol@email.com
Keys — The Most Important Concept in Relational Model
Keys uniquely identify rows and link tables together.
1. Primary Key (PK)
A column (or combination) that uniquely identifies every row in a table.
- Cannot be NULL
- Must be unique
- Every table should have one
CREATE TABLE users (
user_id INT PRIMARY KEY, -- primary key
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
age INT
);
user_id is the primary key.
No two users can have the same user_id.
user_id can never be NULL.
2. Foreign Key (FK)
A column in one table that references the primary key of another table. Creates a link (relationship) between two tables. Enforces referential integrity — you can't reference a row that doesn't exist.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT NOT NULL,
amount DECIMAL(10,2),
status VARCHAR(50),
created_at TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id)
-- order.user_id must match an existing users.user_id
-- you cannot create an order for a user that doesn't exist
);
Table: orders
┌──────────┬─────────┬────────┬───────────┐
│ order_id │ user_id │ amount │ status │
├──────────┼─────────┼────────┼───────────┤
│ 1001 │ 1 │ 500.00 │ CONFIRMED │ ← user_id 1 = Alice
│ 1002 │ 1 │ 200.00 │ SHIPPED │ ← user_id 1 = Alice (same user, another order)
│ 1003 │ 2 │ 750.00 │ PENDING │ ← user_id 2 = Bob
└──────────┴─────────┴────────┴───────────┘
user_id in orders → Foreign Key pointing to user_id in users
3. Candidate Key
Any column (or set of columns) that could serve as a primary key — it is unique and not null.
A table can have multiple candidate keys. You pick one to be the primary key. The rest are called alternate keys.
Table: users
Candidate keys:
- user_id → unique, not null ✅ (chosen as PK)
- email → unique, not null ✅ (alternate key)
- phone → unique, not null ✅ (alternate key — if phone is always provided)
4. Composite Key
A primary key made of two or more columns together — neither alone is unique, but combined they are.
-- A user can enroll in many courses
-- A course can have many users
-- But one user can only enroll in one course ONCE
CREATE TABLE enrollments (
user_id INT,
course_id INT,
enrolled_at TIMESTAMP,
PRIMARY KEY (user_id, course_id) -- composite key
-- user_id alone is not unique (same user, many courses)
-- course_id alone is not unique (same course, many users)
-- together they are unique
);
5. Surrogate Key vs Natural Key
Natural Key → a real-world attribute that is unique
e.g. PAN card number, email, phone number
Surrogate Key → an artificial ID created just for the database
e.g. auto-incremented integer, UUID
Example:
Natural: user.email = "alice@email.com" as primary key
Surrogate: user.user_id = 1, 2, 3 (auto-increment) as primary key
Why surrogate keys are preferred in practice:
- Natural keys can change (email changes, phone changes)
- Natural keys can be long strings — expensive to join on
- Surrogate integer keys are small, fast to index, never change
Key Summary Table
| Key Type | Purpose | Example |
|---|---|---|
| Primary Key | Uniquely identifies each row | user_id in users table |
| Foreign Key | Links to primary key of another table | user_id in orders table |
| Candidate Key | Could be primary key — unique + not null | email in users table |
| Composite Key | Primary key across multiple columns | (user_id, course_id) in enrollments |
| Surrogate Key | Artificial ID (integer/UUID) for identity | order_id INT AUTO_INCREMENT |
| Natural Key | Real-world identifier used as key | PAN card, email, phone |
Relationships Between Tables
Tables in a relational database are connected through keys. Three types of relationships exist.
1. One-to-One (1:1)
One row in Table A corresponds to exactly one row in Table B.
users ──── user_profiles
One user has exactly one profile.
One profile belongs to exactly one user.
users user_profiles
┌─────────┬──────┐ ┌────────────────┬─────────┬──────────┐
│ user_id │ name │ │ profile_id │ user_id │ bio │
├─────────┼──────┤ ├────────────────┼─────────┼──────────┤
│ 1 │ Alice│ │ 101 │ 1 │ "Dev..." │
│ 2 │ Bob │ │ 102 │ 2 │ "PM..." │
└─────────┴──────┘ └────────────────┴─────────┴──────────┘
user_profiles.user_id → FK → users.user_id
When to use: When some data is optional or accessed rarely — split into separate table to keep main table lean.
2. One-to-Many (1:N) — Most Common
One row in Table A corresponds to many rows in Table B.
users ──── orders (one user places many orders)
orders ──── order_items (one order has many items)
products ──── reviews (one product has many reviews)
users orders
┌─────────┬──────┐ ┌──────────┬─────────┬────────┐
│ user_id │ name │ │ order_id │ user_id │ amount │
├─────────┼──────┤ ├──────────┼─────────┼────────┤
│ 1 │ Alice│ │ 1001 │ 1 │ 500 │ ← Alice's order
│ 2 │ Bob │ │ 1002 │ 1 │ 200 │ ← Alice's order
└─────────┴──────┘ │ 1003 │ 2 │ 750 │ ← Bob's order
└──────────┴─────────┴────────┘
Foreign key lives on the "many" side (orders.user_id references users.user_id).
3. Many-to-Many (M:N)
One row in Table A relates to many rows in Table B, and vice versa. Requires a junction table (join table) to store the relationship.
users ←──── enrollments ────→ courses
One user enrolls in many courses.
One course has many users enrolled.
users enrollments courses
┌─────────┐ ┌─────────┬──────────┬──────────────┐ ┌───────────┬────────────┐
│ user_id │ │ user_id │course_id │ enrolled_at │ │ course_id │ name │
├─────────┤ ├─────────┼──────────┼──────────────┤ ├───────────┼────────────┤
│ 1 │ │ 1 │ C01 │ 2024-01-10 │ │ C01 │ System Design│
│ 2 │ │ 1 │ C02 │ 2024-01-11 │ │ C02 │ DSA │
│ 3 │ │ 2 │ C01 │ 2024-01-12 │ │ C03 │ OOP │
└─────────┘ │ 3 │ C03 │ 2024-01-13 │ └───────────┴────────────┘
└─────────┴──────────┴──────────────┘
The enrollments table is the junction table.
Its PK = composite key (user_id, course_id).
Referential Integrity
Referential integrity ensures that relationships between tables stay valid.
Rule: A foreign key value must always match an existing primary key value
in the referenced table — OR be NULL.
Example violation:
orders.user_id = 99 → but users table has no user with user_id = 99
This is a referential integrity violation — the DB prevents it.
-- What happens when you try to delete a user who has orders?
-- The DB enforces integrity via ON DELETE behaviour:
FOREIGN KEY (user_id) REFERENCES users(user_id)
ON DELETE CASCADE -- delete user → automatically delete all their orders
ON DELETE RESTRICT -- delete user → blocked if they have orders (default)
ON DELETE SET NULL -- delete user → set order.user_id = NULL
Schema Design — Putting It Together
A real e-commerce system in relational model:
users ──────────────────── addresses
│ (1:N) (each user has multiple addresses)
│
├── orders ─────────────── order_items ────── products
│ (1:N) (junction) (N:M through order_items)
│
└── reviews ─────────────── products
(M:N simplified)
(each user can review each product once)
payments ──────────── orders
(1:1 or 1:N (one order has one payment, or multiple for split)
depending on design)
-- Minimal schema for above
CREATE TABLE users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT DEFAULT 0
);
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
status ENUM('PENDING','CONFIRMED','SHIPPED','DELIVERED','CANCELLED'),
total DECIMAL(10,2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE RESTRICT
);
CREATE TABLE order_items (
item_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
System Design Context
Why Relational Model Is Used in Most Systems
Flipkart user placing an order:
- user data → users table
- order data → orders table
- items in order → order_items table (junction)
- payment → payments table
- address → addresses table (FK to users)
All these tables are linked via foreign keys.
A single JOIN query can fetch:
"Give me all orders for user Alice,
with all items in each order,
and the product name for each item."
When Relational Model Shines
- Financial data (payments, transactions) — ACID compliance critical
- E-commerce (users, orders, products) — complex relationships
- HR systems, inventory, ERP — structured, consistent data
- Any domain where relationships between entities matter
When It Starts to Struggle
- 100 million rows + heavy joins → performance degrades (indexing helps, but has limits)
- Unstructured data (chat messages, logs) → schema rigidity is a burden
- Horizontal scaling → relational DBs are hard to distribute (this is why NoSQL emerged)
- High write throughput → locking mechanisms create bottlenecks
How This Connects
- SQL - Joins, Aggregations, Subqueries — JOINs are how you follow the foreign-key links to reassemble data that the model splits across tables.
- Normalization - 1NF to BCNF — the discipline that decides which tables exist and where each column lives, so one fact is stored in one place.
- Indexing - B-Tree & Hash Indexes — a primary key is automatically backed by a unique index; foreign-key columns almost always need one too, or joins crawl.
- ACID Properties — referential integrity is the "C" (consistency): the DB refuses any write that would leave a dangling pointer.
- NoSQL Types — document/key-value stores drop foreign keys and joins to scale writes horizontally; this note is the baseline they trade away from.
- Module 4 — Data Architecture — sharding breaks cross-table joins, which is exactly why picking the relational model (or not) is an early design decision.
In the Interview
When to say it. Any schema-design prompt ("design tables for a booking system"), any "how are these related?", any "SQL or NoSQL?" question.
The ~20-second version:
"The relational model stores data as tables — rows are records, columns are attributes, and every row has a primary key that identifies it. Foreign keys link tables:
orders.user_idreferencingusers.user_idmeans every order belongs to a real user, and the database enforces that with referential integrity — you can't insert an order for a user that doesn't exist. Relationships are one-to-one, one-to-many, or many-to-many, and many-to-many always needs a junction table. I reach for relational when the domain is structured with clear relationships and I need ACID — users, orders, payments. The trade-off is that horizontal scaling is hard, which is where NoSQL comes in."
Follow-ups to expect:
- "Surrogate or natural primary key?" — Surrogate (auto-increment int or UUID) in practice: natural keys like email or phone can change and are wider to index and join on.
- "Where does the foreign key go in a one-to-many?" — On the "many" side.
ordersholdsuser_id, not the other way round. - "What happens on delete of a parent row?" — Depends on the FK rule:
RESTRICT(block, the default),CASCADE(delete children too), orSET NULL. - "When does the relational model start to hurt?" — Hundreds of millions of rows with heavy joins, unstructured/variable data, or write throughput that needs many machines.
Mistake to avoid: modelling a many-to-many relationship with a comma-separated column (course_ids = "C01,C02"). It breaks joins, indexing, and integrity — use a junction table.
Quick Revision — 6 Lines
- Relational model = data in tables (rows + columns), linked by keys
- Primary Key uniquely identifies a row — unique, not null, every table needs one
- Foreign Key links to another table's PK — enforces referential integrity
- Three relationships: 1:1, 1:N (most common), M:N (needs junction table)
- Surrogate keys (auto-increment INT) preferred over natural keys in practice
- Relational DBs excel at structured data + complex joins + ACID guarantees