Why this matters
Backend engineers turn real-world data into reliable tables and relationships. Good relational models make queries fast, prevent data loss, and keep code simple. You will use this skill when designing user accounts, orders, permissions, analytics, reporting, and migrations.
- Build features: users sign up, place orders, leave comments.
- Prevent bugs: avoid duplicate rows, orphan records, and inconsistent values.
- Scale safely: support growth without rewriting core data structures.
Real tasks you might face
- Designing tables for authentication (users, sessions, password resets).
- Modeling orders, items, payments, and refunds.
- Representing many-to-many relationships (products ↔ categories, posts ↔ tags).
- Introducing surrogate keys and constraints for better performance and integrity.
Concept explained simply
A relational model represents data as tables (relations). Each table has rows (records) and columns (attributes). Relationships connect tables using keys so data stays consistent and queryable.
Mental model
Think of tables as spreadsheets with strict rules:
- Every row is unique (enforced by a primary key).
- Columns have clear meanings and types.
- Relationships are explicit (foreign keys) so related rows always exist.
Why keys matter
Primary keys uniquely identify rows. Foreign keys reference other tables, enforcing integrity. Without keys, data turns into a messy set of spreadsheets with no guarantees.
Core building blocks
Entities (tables)
Represent real things: User, Order, Product. Name tables in singular (optional preference) and be consistent.
Attributes (columns)
- Use clear types (INT, UUID, VARCHAR, DATE, DECIMAL).
- Prefer atomic values (no comma-separated lists in one column).
Keys
- Primary Key (PK): unique identifier. Often an auto-increment INT or UUID.
- Foreign Key (FK): references a PK in another table.
- Candidate Key: any column or set of columns that could be a PK.
- Composite Key: key made of multiple columns (e.g., order_id + product_id).
Relationships
- One-to-One (1:1): rare; split sensitive or optional details.
- One-to-Many (1:N): most common; e.g., User → Orders.
- Many-to-Many (M:N): use a junction table; e.g., PostTags(post_id, tag_id).
Referential integrity
- Foreign keys ensure referenced rows exist.
- Cascades: ON DELETE/UPDATE rules (RESTRICT, CASCADE, SET NULL) control what happens when parents change.
Choosing cascade rules
- RESTRICT: stop accidental deletes (safe default for critical parents like Users).
- CASCADE: remove dependent rows automatically (e.g., Order → OrderItems).
- SET NULL: keep child but mark as missing (e.g., optional relationships).
Worked examples
Example 1: Users and Orders (1:N)
Goal: A user can have many orders; an order belongs to one user.
Model
Users(id PK, email UNIQUE, created_at) Orders(id PK, user_id FK→Users.id, total_amount DECIMAL, placed_at) FK: Orders.user_id references Users.id (RESTRICT delete, to preserve history)
Why this works
- Each order links to exactly one user via user_id.
- We protect orders from accidental user deletion (history matters).
Example 2: Posts and Tags (M:N)
Goal: A post can have many tags; a tag can belong to many posts.
Model
Posts(id PK, title, body) Tags(id PK, name UNIQUE) PostTags(post_id FK→Posts.id, tag_id FK→Tags.id, PRIMARY KEY (post_id, tag_id)) FKs with CASCADE delete from Posts → PostTags, and from Tags → PostTags
Why this works
- Junction table PostTags resolves many-to-many relationships.
- Composite PK prevents duplicate pairs (same tag on same post twice).
Example 3: Orders and OrderItems (Composite Key)
Goal: An order has multiple line items; each line links to a product at a specific price/quantity.
Model
Products(id PK, name, current_price)
Orders(id PK, user_id FK→Users.id, placed_at)
OrderItems(order_id FK→Orders.id, product_id FK→Products.id,
unit_price DECIMAL, qty INT,
PRIMARY KEY (order_id, product_id))
FK delete: CASCADE from Orders → OrderItemsWhy this works
- Composite PK (order_id, product_id) ensures one line per product per order.
- unit_price is stored to preserve historical price at purchase time.
Example 4: Normalization (1NF → 3NF)
Start (bad): Orders(id, user_email, items CSV, total_amount). Problems: repeated data, hard updates.
Normalize
- 1NF: Make items atomic using OrderItems table.
- 2NF: Move user_email into Users table; reference via user_id.
- 3NF: Ensure non-key columns depend only on the key (e.g., total_amount should be derived or validated).
Users(id PK, email UNIQUE) Orders(id PK, user_id FK→Users.id, placed_at) OrderItems(order_id FK, product_id FK, unit_price, qty, PRIMARY KEY(order_id, product_id))
How to design step-by-step
- List entities: nouns in requirements (User, Order, Product).
- Define attributes: types and nullability.
- Choose primary keys: surrogate (INT/UUID) or natural; be consistent.
- Map relationships: 1:1, 1:N, M:N; add FKs or a junction table.
- Set constraints: NOT NULL, UNIQUE, CHECK, FK with delete/update actions.
- Normalize: aim for 3NF; denormalize only for clear performance needs.
- Test with queries: can you fetch common use cases easily and efficiently?
Quick self-check checklist
Exercises
Try these. Then compare with the solutions provided below each exercise.
Exercise 1: Model a simple store (Customers, Orders, Products)
Design tables for Customers, Products, Orders, and OrderItems. Add appropriate PKs, FKs, and constraints. Include unit_price on OrderItems.
What to produce
- Table names and columns (with PK/FK markings).
- Relationship descriptions and delete rules.
Exercise 2: Normalize a course registration sheet
You receive a single sheet with columns: student_email, student_name, course_code, course_title, instructor_name. Normalize to 3NF with appropriate keys and relationships.
Tip
Expect a junction table for enrollments.
Common mistakes and how to self-check
- Storing lists in one column (e.g., tag_ids="1,2,3"). Fix: use a junction table.
- Missing unique constraints on natural keys (emails, codes). Fix: add UNIQUE.
- Orphan records caused by missing FKs. Fix: enforce FK constraints.
- Overusing CASCADE. Fix: cascade only when child rows have no meaning without the parent.
- Premature denormalization. Fix: normalize first; denormalize with evidence.
Self-check prompts
- Can I delete a parent safely? What happens to children?
- Can I answer common business questions with simple joins?
- Am I duplicating business-critical text (titles, names) across tables?
Practical projects
- Blog data model: posts, authors, comments, tags, PostTags.
- Inventory and sales: products, stock movements, orders, order items, payments.
- Learning portal: students, courses, instructors, enrollments, grades.
Project checklist
Learning path
- Relational modeling basics (this lesson).
- SQL constraints and indexing fundamentals.
- Query design and performance (joins, aggregations).
- Migrations and schema evolution.
- Transactions, isolation, and data integrity at scale.
Who this is for
- Backend engineers and students starting with relational databases.
- Developers moving from NoSQL who need strong consistency and joins.
Prerequisites
- Basic SQL (SELECT/INSERT/UPDATE/DELETE).
- Familiarity with data types and indexing concepts helps but not required.
Next steps
- Complete the exercises and verify with the solutions.
- Apply the patterns to one of the practical projects.
- Take the Quick Test to confirm your understanding.
Quick Test
Take the Quick Test below to check your knowledge. It’s available to everyone; if you sign in, your progress will be saved.
Mini challenge
Design a small library system:
- Entities: Member, Book, Author, Loan.
- Requirements: a book can have multiple authors; a member can borrow many books; keep loan history; deleting a member should not delete books.
Hints
- Use a junction table for Book ↔ Author.
- Loans should reference Member and Book, with dates and return status.
- Choose RESTRICT on Book deletion; CASCADE from Book to BookAuthors may be fine.