Chapter 8: Database Design

Normalization, Constraints, and Schema Planning

8.1 The Relational Model

A relational database stores data in tables (relations). Each table has a fixed set of columns with defined data types and a collection of rows. A primary key uniquely identifies each row. The relational model enforces structure, which makes querying predictable and efficient.

8.2 Data Types

8.3 Primary Keys and Foreign Keys

-- Primary key: uniquely identifies each row
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    first_name  TEXT NOT NULL,
    last_name   TEXT NOT NULL,
    email       TEXT UNIQUE
);

-- Foreign key: references another table's primary key
CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date  TEXT NOT NULL,
    total       REAL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Composite primary key (for junction/bridge tables)
CREATE TABLE order_items (
    order_id   INTEGER,
    product_id INTEGER,
    quantity   INTEGER NOT NULL DEFAULT 1,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id)   REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

8.4 Constraints

CREATE TABLE employees (
    emp_id      INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,                      -- cannot be NULL
    email       TEXT UNIQUE,                        -- no duplicates
    department  TEXT DEFAULT 'General',             -- default value
    salary      REAL CHECK(salary > 0),             -- must be positive
    hire_date   TEXT NOT NULL,
    manager_id  INTEGER,
    FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);

8.5 Normalization

Normalization organizes tables to reduce redundancy and prevent update anomalies. The three normal forms (1NF, 2NF, 3NF) provide a roadmap.

First Normal Form (1NF)

Each cell contains a single value; each row is unique. Violated when a column holds comma-separated lists.

-- BAD: violates 1NF (multiple values in one cell)
-- | order_id | products          |
-- | 1        | Widget A, Cable C  |

-- GOOD: separate table for order items
-- orders: order_id, customer_id, order_date
-- order_items: order_id, product_id, quantity

Second Normal Form (2NF)

Every non-key column depends on the entire primary key (relevant for composite keys).

-- BAD: product_name depends only on product_id, not on order_id
-- | order_id | product_id | product_name | quantity |
-- Splitting into orders, products, and order_items fixes this.

Third Normal Form (3NF)

No transitive dependencies: non-key columns depend only on the primary key, not on other non-key columns.

-- BAD: city depends on zip_code, not on customer_id
-- | customer_id | zip_code | city     |

-- GOOD: separate zip_codes table
CREATE TABLE zip_codes (
    zip_code TEXT PRIMARY KEY,
    city     TEXT,
    state    TEXT
);

8.6 Schema Design Patterns

Star Schema (Data Warehousing)

-- Fact table: one row per event/transaction
CREATE TABLE fact_sales (
    sale_id     INTEGER PRIMARY KEY,
    date_key    INTEGER,
    product_key INTEGER,
    store_key   INTEGER,
    quantity    INTEGER,
    revenue     REAL
);

-- Dimension tables: descriptive attributes
CREATE TABLE dim_date (
    date_key    INTEGER PRIMARY KEY,
    full_date   TEXT,
    year        INTEGER,
    quarter     INTEGER,
    month       INTEGER,
    day_of_week TEXT
);

CREATE TABLE dim_product (
    product_key  INTEGER PRIMARY KEY,
    product_name TEXT,
    category     TEXT,
    brand        TEXT
);

8.7 ALTER TABLE

-- Add a column
ALTER TABLE customers ADD COLUMN phone TEXT;

-- Rename a column (SQLite 3.25+)
ALTER TABLE customers RENAME COLUMN phone TO phone_number;

-- Rename a table
ALTER TABLE customers RENAME TO clients;

-- Drop a column (SQLite 3.35+, PostgreSQL)
ALTER TABLE clients DROP COLUMN phone_number;