Database Design

Writing SQL queries is one skill. Designing the tables they query is another. A well-designed database is easy to query, resistant to data anomalies, and straightforward to maintain. A poorly designed one leads to duplicated data, inconsistent records, and queries that feel like solving a puzzle. In this lesson, you'll learn the principles that separate good designs from bad ones.

The Problem with Bad Design

Let's start by looking at what happens when you put everything in one table.

-- A poorly designed "flat" table
CREATE TABLE orders_flat (
    order_id INTEGER PRIMARY KEY,
    customer_name TEXT,
    customer_email TEXT,
    customer_city TEXT,
    product_name TEXT,
    product_price REAL,
    product_category TEXT,
    quantity INTEGER,
    order_date TEXT
);

INSERT INTO orders_flat VALUES
    (1, 'Alice Johnson', 'alice@example.com', 'New York', 'Laptop', 999.99, 'Electronics', 1, '2025-01-10'),
    (2, 'Alice Johnson', 'alice@example.com', 'New York', 'Mouse', 24.99, 'Electronics', 2, '2025-01-10'),
    (3, 'Bob Smith', 'bob@example.com', 'London', 'Laptop', 999.99, 'Electronics', 1, '2025-01-12'),
    (4, 'Alice Johnson', 'alice@new-email.com', 'New York', 'Keyboard', 79.99, 'Electronics', 1, '2025-01-15');

-- Notice the problems:
-- 1. Alice's email is inconsistent (rows 1-2 vs row 4)
-- 2. Product info is duplicated (Laptop appears twice)
-- 3. Updating Alice's email requires finding ALL her rows
SELECT * FROM orders_flat;

This table has three classic problems:

Update anomaly — Alice's email changed in one row but not others.
Insertion anomaly — You can't add a new product without creating a fake order.
Deletion anomaly — Deleting Bob's order loses his customer information entirely.

Normalization: First Normal Form (1NF)

A table is in 1NF when:

Each column contains atomic (indivisible) values
Each row is unique
There are no repeating groups

-- BAD: violates 1NF with comma-separated values
CREATE TABLE contacts_bad (
    id INTEGER PRIMARY KEY,
    name TEXT,
    phone_numbers TEXT -- "555-0100, 555-0101, 555-0102"
);

INSERT INTO contacts_bad VALUES
    (1, 'Alice', '555-0100, 555-0101'),
    (2, 'Bob', '555-0200');

-- Searching for a specific number is painful:
SELECT * FROM contacts_bad WHERE phone_numbers LIKE '%555-0101%';

-- GOOD: 1NF with a separate table for phone numbers
CREATE TABLE contacts (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE phone_numbers (
    id INTEGER PRIMARY KEY,
    contact_id INTEGER NOT NULL,
    phone TEXT NOT NULL,
    type TEXT DEFAULT 'mobile'
);

INSERT INTO contacts VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO phone_numbers VALUES
    (1, 1, '555-0100', 'mobile'),
    (2, 1, '555-0101', 'work'),
    (3, 2, '555-0200', 'mobile');

-- Now searching is clean and precise
SELECT c.name, p.phone, p.type
FROM contacts c
JOIN phone_numbers p ON c.id = p.contact_id
WHERE p.phone = '555-0101';

Second Normal Form (2NF) and Third Normal Form (3NF)

2NF removes partial dependencies — every non-key column must depend on the entire primary key. 3NF removes transitive dependencies — non-key columns shouldn't depend on other non-key columns.

-- Before normalization: one big table
CREATE TABLE order_details_bad (
    order_id INTEGER,
    product_id INTEGER,
    product_name TEXT,        -- depends only on product_id (partial dependency)
    product_category TEXT,    -- depends only on product_id
    customer_name TEXT,       -- depends only on order_id (partial dependency)
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

-- After normalization: properly separated tables

-- Customers table
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    city TEXT
);

-- Products table
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT,
    price REAL NOT NULL
);

-- Orders table (references customers)
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date TEXT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Order items (references orders and products)
CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 1,
    unit_price REAL NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Insert normalized data
INSERT INTO customers VALUES (1, 'Alice Johnson', 'alice@example.com', 'New York');
INSERT INTO customers VALUES (2, 'Bob Smith', 'bob@example.com', 'London');

INSERT INTO products VALUES (1, 'Laptop', 'Electronics', 999.99);
INSERT INTO products VALUES (2, 'Mouse', 'Electronics', 24.99);
INSERT INTO products VALUES (3, 'Keyboard', 'Electronics', 79.99);

INSERT INTO orders VALUES (1, 1, '2025-01-10');
INSERT INTO orders VALUES (2, 2, '2025-01-12');

INSERT INTO order_items VALUES (1, 1, 1, 1, 999.99);
INSERT INTO order_items VALUES (2, 1, 2, 2, 24.99);
INSERT INTO order_items VALUES (3, 2, 1, 1, 999.99);

-- Query the normalized data
SELECT
    c.name AS customer,
    o.order_date,
    p.name AS product,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price AS line_total
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
JOIN customers c ON o.customer_id = c.id
JOIN products p ON oi.product_id = p.id
ORDER BY o.id, oi.id;

Now each fact is stored exactly once. Updating Alice's email means changing one row in the customers table.

Primary Keys and Foreign Keys

Primary keys uniquely identify each row. Foreign keys establish relationships between tables and enforce referential integrity.

-- Enable foreign key enforcement in SQLite
PRAGMA foreign_keys = ON;

-- Create tables with proper key relationships
CREATE TABLE authors (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    bio TEXT
);

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author_id INTEGER NOT NULL,
    published_year INTEGER,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

CREATE TABLE reviews (
    id INTEGER PRIMARY KEY,
    book_id INTEGER NOT NULL,
    rating INTEGER CHECK(rating BETWEEN 1 AND 5),
    comment TEXT,
    FOREIGN KEY (book_id) REFERENCES books(id)
);

-- Insert valid data
INSERT INTO authors VALUES (1, 'Jane Austen', 'English novelist');
INSERT INTO authors VALUES (2, 'George Orwell', 'English essayist and novelist');

INSERT INTO books VALUES (1, 'Pride and Prejudice', 1, 1813);
INSERT INTO books VALUES (2, '1984', 2, 1949);
INSERT INTO books VALUES (3, 'Animal Farm', 2, 1945);

INSERT INTO reviews VALUES (1, 1, 5, 'A timeless classic');
INSERT INTO reviews VALUES (2, 2, 5, 'Terrifyingly relevant');
INSERT INTO reviews VALUES (3, 2, 4, 'Must read');

-- Query across relationships
SELECT
    b.title,
    a.name AS author,
    COUNT(r.id) AS review_count,
    ROUND(AVG(r.rating), 1) AS avg_rating
FROM books b
JOIN authors a ON b.author_id = a.id
LEFT JOIN reviews r ON b.id = r.book_id
GROUP BY b.id, b.title, a.name
ORDER BY avg_rating DESC;

Constraints for Data Integrity

Constraints are rules enforced by the database to keep data valid.

-- Create a table with various constraints
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    department TEXT NOT NULL DEFAULT 'Unassigned',
    salary INTEGER CHECK(salary > 0),
    hire_date TEXT NOT NULL,
    manager_id INTEGER,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

-- Valid inserts
INSERT INTO employees (id, email, name, department, salary, hire_date)
VALUES (1, 'sarah@company.com', 'Sarah CEO', 'Executive', 150000, '2015-01-01');

INSERT INTO employees (id, email, name, department, salary, hire_date, manager_id)
VALUES (2, 'tom@company.com', 'Tom Engineer', 'Engineering', 95000, '2020-03-15', 1);

INSERT INTO employees (id, email, name, department, salary, hire_date, manager_id)
VALUES (3, 'lisa@company.com', 'Lisa Designer', 'Design', 85000, '2021-06-01', 1);

-- These would fail:
-- Duplicate email: INSERT INTO employees VALUES (4, 'tom@company.com', ...);
-- NULL name: INSERT INTO employees (id, email, name) VALUES (4, 'test@co.com', NULL);
-- Negative salary: INSERT INTO employees (..., salary, ...) VALUES (..., -1000, ...);

SELECT id, email, name, department, salary, manager_id
FROM employees;

Key constraint types:

NOT NULL — Column must have a value
UNIQUE — No duplicate values allowed
CHECK — Value must satisfy a condition
DEFAULT — Provides a fallback value
FOREIGN KEY — References must point to existing rows
PRIMARY KEY — Combines NOT NULL and UNIQUE

Entity-Relationship Modeling

Before writing CREATE TABLE statements, sketch out your entities and their relationships.

Common Relationship Patterns

-- ONE-TO-MANY: One department has many employees
CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- MANY-TO-MANY: Students enroll in many courses; courses have many students
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);

-- Junction table for the many-to-many relationship
CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    enrolled_date TEXT,
    grade TEXT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- Insert sample data
INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Marketing');
INSERT INTO employees VALUES (1, 'Alice', 1), (2, 'Bob', 1), (3, 'Carol', 2);

INSERT INTO students VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO courses VALUES (1, 'SQL 101'), (2, 'Web Dev'), (3, 'Data Science');

INSERT INTO enrollments VALUES (1, 1, '2025-01-05', 'A');
INSERT INTO enrollments VALUES (1, 2, '2025-01-05', 'B');
INSERT INTO enrollments VALUES (2, 1, '2025-01-06', 'A');
INSERT INTO enrollments VALUES (3, 2, '2025-01-07', NULL);
INSERT INTO enrollments VALUES (3, 3, '2025-01-07', NULL);

-- Query many-to-many: which students are in which courses?
SELECT
    s.name AS student,
    c.title AS course,
    e.grade
FROM enrollments e
JOIN students s ON e.student_id = s.id
JOIN courses c ON e.course_id = c.id
ORDER BY s.name, c.title;

-- How many students per course?
SELECT c.title, COUNT(e.student_id) AS enrollment_count
FROM courses c
LEFT JOIN enrollments e ON c.id = e.course_id
GROUP BY c.id, c.title;

The junction table (enrollments) is the standard way to implement many-to-many relationships. It contains foreign keys to both tables plus any attributes that belong to the relationship itself (like grade and enrolled_date).

Practice Exercises

Try these on your own:

Take a flat table with customer, order, and product data and normalize it into 3NF
Design a schema for a blog with authors, posts, tags (many-to-many), and comments
Add appropriate constraints (NOT NULL, UNIQUE, CHECK, FOREIGN KEY) to a schema
Create a junction table for a many-to-many relationship between employees and projects
Identify the normalization form of a given table and explain what changes are needed

Key Takeaways

Normalization reduces data redundancy and prevents update, insert, and delete anomalies
1NF: atomic values, no repeating groups. 2NF: no partial dependencies. 3NF: no transitive dependencies
Primary keys uniquely identify rows; foreign keys link related tables
Constraints (NOT NULL, UNIQUE, CHECK, FOREIGN KEY) enforce data integrity at the database level
Many-to-many relationships use junction tables with foreign keys to both sides
Design your schema before writing queries — it's much harder to fix a bad design later

Pro Tip: Don't over-normalize. If you find yourself joining 7 tables for a simple query, you may have gone too far. Sometimes controlled redundancy (denormalization) is acceptable for performance-critical read-heavy applications. The key is making that decision consciously, not accidentally.

Next Steps

Now that you know how to design databases well, you're ready to learn about migrations — how to evolve your schema safely as requirements change!