401 — Database Design and Normalization

Advanced

Learn to design relational databases from scratch in a separate SQLite design lab. Understand normalization forms (1NF, 2NF, 3NF), entity relationships, junction tables, and when to break the rules and denormalize for performance.

Learning Objectives

Understand why database design matters for data integrity and performance

Think in entities and relationships before writing SQL

Apply First, Second, and Third Normal Forms to eliminate redundancy

Normalize a flat table step by step

Model one-to-many and many-to-many relationships

Use junction tables for many-to-many relationships

Know when denormalization is the right trade-off

Design a schema from business requirements and implement it with constraints

Identify the grain of each table before deciding where a column belongs

Step 1

Why database design matters

Start with a badly designed flat table to see the problems poor design causes. Create a single orders table that stores everything in one place.

Commands to Run

sqlite3 design_lab.db

CREATE TABLE bad_orders (
  order_id INTEGER,
  order_date TEXT,
  customer_name TEXT,
  customer_email TEXT,
  customer_city TEXT,
  product_name TEXT,
  product_price REAL,
  quantity INTEGER
);

INSERT INTO bad_orders VALUES (1, '2024-01-15', 'Alice Johnson', 'alice@example.com', 'Portland', 'Laptop', 999.99, 1);

INSERT INTO bad_orders VALUES (2, '2024-01-16', 'Alice Johnson', 'alice@example.com', 'Portland', 'Mouse', 29.99, 2);

INSERT INTO bad_orders VALUES (3, '2024-01-16', 'Bob Smith', 'bob@example.com', 'Seattle', 'Laptop', 999.99, 1);

INSERT INTO bad_orders VALUES (4, '2024-01-17', 'Alice Johnson', 'alice_new@example.com', 'Portland', 'Keyboard', 79.99, 1);

SELECT * FROM bad_orders;

What This Does

This flat table has real problems. Alice's email changed in order 4, but orders 1 and 2 still have her old email. If a laptop's price changes, you'd need to update every row that mentions it. And if Bob cancels his only order, you lose all record that Bob exists. These are update, insertion, and deletion anomalies. They are common signs of poor database design.

Expected Outcome

You see four rows with repeated customer and product information. Notice Alice's email is inconsistent between rows — this is exactly the kind of bug bad design causes.

Pro Tips

1
Ask yourself: 'If I update one fact, how many rows do I need to change?'
2
Redundant data always drifts out of sync eventually
3
A well-designed database makes incorrect data hard to store

Common Mistakes to Avoid

⚠️Thinking flat tables are fine because they're simple — they cause real bugs in production
⚠️Storing the same fact (like a customer email) in multiple rows

Was this step helpful?