MENU


Database Design and Normalization

Most shops start with a spreadsheet, one row per order line, with the customer and the book copied onto every row. This section normalizes one into the shop schema of Section 3.4. Rebuild it by flattening the sample data, adding an authors cell the way people type it:

CREATE TABLE sheet (PRIMARY KEY (order_no, sku)) AS

SELECT o.id AS order_no, o.status, c.email, c.name AS cust_name, c.country, p.sku, p.title,

g.name AS category, i.unit_price AS price, i.quantity AS qty,

ELT(p.id, 'Maya Lind, Omar Haddad', 'Rui Costa', 'Rui Costa, Lena Vogel', 'Omar Haddad',

'Ines Park', 'Tom Ito') AS authors

FROM order_items i JOIN orders o ON o.id = i.order_id JOIN customers c ON c.id = o.customer_id

JOIN products p ON p.id = i.product_id JOIN categories g ON g.id = p.category_id;

SELECT order_no, cust_name, sku, category, price, qty, authors

FROM sheet WHERE order_no IN (1, 6);

Listing 3.86: The spreadsheet the shop kept before it had a database

+----------+-------------+-----------+-------------+-------+-----+------------------------+

| order_no | cust_name | sku | category | price | qty | authors |

+----------+-------------+-----------+-------------+-------+-----+------------------------+

| 1 | Ana Souza | AC-MUG-01 | Accessories | 12.50 | 2 | NULL |

| 1 | Ana Souza | BK-PHP-01 | Programming | 39.90 | 1 | Maya Lind, Omar Haddad |

| 6 | Elif Yilmaz | BK-LAR-01 | Programming | 49.00 | 1 | Omar Haddad |

| 6 | Elif Yilmaz | BK-PHP-01 | Programming | 39.90 | 2 | Maya Lind, Omar Haddad |

+----------+-------------+-----------+-------------+-------+-----+------------------------+

Output of Listing 3.86 A fact stored twice invites three anomalies: an update anomaly changes one copy but not the others, an insert anomaly cannot record a fact without an unrelated one, and a delete anomaly loses facts that lived only in the deleted rows. All three, rolled back:

START TRANSACTION;

UPDATE sheet SET cust_name = 'Benjamin Carter' WHERE order_no = 9;

INSERT INTO sheet (sku, title, price) VALUES ('BK-API-01', 'REST APIs in PHP', 36.00);

DELETE FROM sheet WHERE order_no = 5;

SELECT (SELECT COUNT(DISTINCT cust_name) FROM sheet WHERE email LIKE 'ben@%') AS ben_names,

(SELECT COUNT(*) FROM sheet WHERE email = 'dara@example.com' OR sku = 'BK-UX-01') AS dara;

ROLLBACK;

Listing 3.87: Update, insert and delete anomalies in the sheet

ERROR 1364 (HY000): Field 'email' doesn't have a default value

+-----------+------+

| ben_names | dara |

+-----------+------+

| 2 | 0 |

+-----------+------+

Output of Listing 3.87 Ben has two names, a new book cannot exist until someone orders it, and canceling Dara's only order erased Dara and the only copy of Designing Calm Interfaces.

3.10.1 Entities, Attributes, and Relationships

An entity-relationship (ER) model, proposed by Peter Chen in 1976, describes data before any table exists. An entity is a thing you store facts about, an attribute is one such fact, and a relationship connects entities. In the sheet, email, cust_name and country describe a customer, sku, title and authors a book, and status an order, while qty and price describe one book on one order: a relationship with attributes of its own. Kind Shop example Becomes Simple price, country One column Composite a postal address One column per part you query Derived an order total Computed, or a generated column (3.4.8) Multi-valued a book's authors A separate table (3.10.5) Table 3.5: Kinds of attributes and where each ends up Something with facts of its own is an entity: a category has a name and a parent. A country code has nothing else the shop stores, so it stays an attribute of the customer.

3.10.2 Cardinality and ER Diagram Notation

Cardinality says how many rows on each side of a relationship can pair up. Its maximum gives the shapes 1:1, 1:N and N:M; its minimum is zero (optional) or one (mandatory). Crow's foot notation, introduced by Gordon Everest in 1976 and drawn by MySQL Workbench, puts the maximum next to the entity (a bar for one, a fork for many) and the minimum behind it (a circle for zero, a bar for one). Read from one entity to the far end: "one order contains one or more items."Figure 3.8: The shop in crow's foot notation. Each end shows how many rows of the entity beside it pair with one row at the far endOrders and products are N:M, drawn here already resolved into the associative entity Order item and two 1:N lines. Categories form a recursive relationship. Cardinality comes from business rules, so ask the owner; no foreign key can enforce "at least one item," so PHP must.

3.10.3 Translating an ER Model into Tables

Each entity becomes a table whose key is its primary key. A 1:N relationship becomes a foreign key on the "many" side (orders.customer_id). A 1:1 becomes a foreign key with a UNIQUE constraint on the optional side, or one merged table. An N:M becomes a junction table with a foreign key to each side and the pair as its primary key, and so does a multi-valued attribute whose values have facts of their own. order_items is the shop's junction table, and the sheet's authors need another:

CREATE TABLE authors (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL);

CREATE TABLE product_authors (

product_id INT UNSIGNED NOT NULL, author_id INT UNSIGNED NOT NULL,

position TINYINT UNSIGNED NOT NULL DEFAULT 1,

PRIMARY KEY (product_id, author_id),

FOREIGN KEY (product_id) REFERENCES products (id),

FOREIGN KEY (author_id) REFERENCES authors (id)

);

Listing 3.88: Books and authors, an N:M relationship, through a junction table The pair key forbids listing an author twice on a book, and position (byline order) describes the pairing. MySQL adds an index named author_id for the foreign key, so both directions are fast.

3.10.4 Functional Dependencies

A functional dependency X → Y says each value of X comes with exactly one value of Y; X is the determinant. A candidate key is a minimal set of columns that determines all the others. With the key (order_no, sku), the sheet holds:

  • order_no → status, email and sku → title, category: partial dependencies on part of the key;

  • email → cust_name, country: transitive, reaching the key only through email;

  • (order_no, sku) → qty, price: a full dependency on the whole key.

    • Business rules define dependencies, but data can refute one, by grouping on the determinant:

    SELECT country, COUNT(DISTINCT email) AS emails FROM sheet GROUP BY country HAVING emails > 1;

    SELECT order_no, cust_name, SUM(qty) AS items FROM sheet GROUP BY order_no;

    Listing 3.89: Refuting country → email, then MySQL's own dependency check

    +---------+--------+

    | country | emails |

    +---------+--------+

    | MY | 2 |

    +---------+--------+

    ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains

    nonaggregated column 'shop.sheet.cust_name' which is not functionally dependent on columns

    in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

    Output of Listing 3.89 The same test finds no violation of email → cust_name, but passing proves nothing: sku → price passes too, yet a sale would break it, so order lines keep their own unit_price. ONLY_FULL_GROUP_BY accepts a non-grouped column only when a key or an equality makes it dependent. The sheet declares none; the same report on shop runs (Section 3.5.6).

    3.10.5 First Normal Form

    A table is in first normal form (1NF) when each column holds one atomic value, no group of columns repeats (author1, author2), and a key identifies every row. The authors cell fails: finding Omar Haddad's books takes LIKE '%Haddad%', which cannot use an index and would match "Haddadi" too. Give each value its own row; JSON_TABLE (Section 3.9.5) splits all lists at once:

    CREATE TABLE sku_authors (PRIMARY KEY (sku, author)) AS

    SELECT DISTINCT s.sku, TRIM(j.author) AS author

    FROM sheet AS s, JSON_TABLE(CONCAT('["', REPLACE(s.authors, ',', '","'), '"]'),

    '$[*]' COLUMNS (author VARCHAR(40) PATH '$')) AS j;

    ALTER TABLE sheet DROP COLUMN authors;

    SELECT author, GROUP_CONCAT(sku ORDER BY sku) AS skus

    FROM sku_authors GROUP BY author HAVING COUNT(*) > 1;

    Listing 3.90: Splitting a comma-separated cell into one row per value

    +-------------+---------------------+

    | author | skus |

    +-------------+---------------------+

    | Omar Haddad | BK-LAR-01,BK-PHP-01 |

    | Rui Costa | BK-SQL-01,BK-SQL-02 |

    +-------------+---------------------+

    Output of Listing 3.90 Authors with several books are now a plain GROUP BY, and a join maps these rows onto product_authors (Section 3.10.3). Keep a list as JSON only when you never join, count or constrain its elements, as with products.attributes.

    3.10.6 Second and Third Normal Forms

    Second normal form (2NF) forbids partial dependencies and third normal form (3NF) transitive ones; Codd defined both in 1971. In William Kent's words, every non-key column states a fact about "the key, the whole key, and nothing but the key." The fix: move the dependent columns into a table keyed by their determinant, and leave the determinant behind as a foreign key.

    CREATE TABLE products_2nf (PRIMARY KEY (sku)) AS

    SELECT DISTINCT sku, title, category FROM sheet;

    CREATE TABLE lines_2nf (PRIMARY KEY (order_no, sku)) AS

    SELECT order_no, sku, price, qty FROM sheet;

    CREATE TABLE orders_2nf (PRIMARY KEY (order_no)) AS

    SELECT DISTINCT order_no, status, email, cust_name, country FROM sheet;

    CREATE TABLE customers_3nf (PRIMARY KEY (email)) AS

    SELECT DISTINCT email, cust_name, country FROM orders_2nf;

    ALTER TABLE orders_2nf DROP COLUMN cust_name, DROP COLUMN country;

    SELECT COUNT(*) AS lost FROM (TABLE sheet EXCEPT

    SELECT order_no, status, email, cust_name, country, sku, title, category, price, qty

    FROM lines_2nf JOIN orders_2nf USING (order_no) JOIN customers_3nf USING (email)

    JOIN products_2nf USING (sku)) AS d;

    Listing 3.91: Decomposing the sheet into 2NF, then 3NF, and checking that nothing was lost

    +------+

    | lost |

    +------+

    | 0 |

    +------+

    Output of Listing 3.91 The join of 16 lines, 9 orders, 6 customers and 8 products rebuilds every sheet row, and joins along keys cannot invent rows, so the split is lossless. To reach shop, categories get a table and surrogate id columns replace natural keys (Section 3.10.8). The anomalies are gone:

    START TRANSACTION;

    UPDATE customers SET name = 'Benjamin Carter' WHERE email = 'ben@example.com';

    INSERT INTO products (sku, title, category_id, price) VALUES ('BK-API-01', 'REST APIs', 2, 36);

    DELETE FROM order_items WHERE order_id = 5;

    DELETE FROM orders WHERE id = 5;

    SELECT (SELECT COUNT(DISTINCT name) FROM customers WHERE email = 'ben@example.com') AS ben,

    (SELECT COUNT(*) FROM products WHERE sku IN ('BK-API-01', 'BK-UX-01')) AS books,

    (SELECT COUNT(*) FROM customers WHERE email = 'dara@example.com') AS dara;

    ROLLBACK;

    Listing 3.92: The three anomalies are gone in the normalized schema

    +------+-------+------+

    | ben | books | dara |

    +------+-------+------+

    | 1 | 2 | 1 |

    +------+-------+------+

    Output of Listing 3.92 Ben's name lives in one row, a book can exist unsold, and Dara and her book outlive the order.

    3.10.7 Boyce-Codd, Fourth, and Fifth Normal Forms

    Boyce-Codd normal form (BCNF), from 1974, requires every determinant to be a candidate key, which differs from 3NF only when keys overlap. With unique titles stored on order lines, both (order_no, sku) and (order_no, title) are keys, and sku → title passes 3NF because title is part of a key. But sku alone is no key, so the title repeats per sale and can drift:

    CREATE TABLE lines_titled (order_no INT, sku VARCHAR(12), title VARCHAR(60), qty INT,

    PRIMARY KEY (order_no, sku), UNIQUE (order_no, title));

    INSERT INTO lines_titled VALUES (1, 'BK-PHP-01', 'Modern PHP in Practice', 1),

    (6, 'BK-PHP-01', 'Modern PHP, 2nd Edition', 2);

    SELECT sku, COUNT(DISTINCT title) AS titles FROM lines_titled GROUP BY sku;

    Listing 3.93: A table in 3NF but not in BCNF accepts two titles for one book

    +-----------+--------+

    | sku | titles |

    +-----------+--------+

    | BK-PHP-01 | 2 |

    +-----------+--------+

    Output of Listing 3.93 The title belongs in products, where sku is a key. Fourth normal form (4NF) (Fagin, 1977) separates independent lists: a table of a book's authors and formats needs all 6 pairs for 2 authors and 3 formats, where two tables need 5 rows. Fifth normal form (5NF) splits a three-way table into pairs only when a rule guarantees the pairs rejoin exactly. Aim for BCNF.

    3.10.8 Surrogate Versus Natural Keys

    A natural key means something (an email, a SKU); a surrogate key only identifies the row. Natural keys change, dragging every foreign key along, and InnoDB copies the primary key into each secondary index, so shop uses narrow surrogates and keeps natural keys UNIQUE. Natural keys suit stable codes (country) and junction tables. Use a UUID when ids are made outside the database or must not be guessable. UUID() returns version 1, which starts with the fastest-changing timestamp bits; UUID_TO_BIN(u, 1) packs it into BINARY(16) with the first and third groups swapped, so later values sort later:

    SET @u = UUID();

    SELECT @u AS uuid, HEX(UUID_TO_BIN(@u, 1)) AS packed;

    Listing 3.94: Packing a UUID into 16 bytes, time-ordered

    +--------------------------------------+----------------------------------+

    | uuid | packed |

    +--------------------------------------+----------------------------------+

    | 98bfb64a-b727-11f1-98d6-00155d5f9c3f | 11F1B72798BFB64A98D600155D5F9C3F |

    +--------------------------------------+----------------------------------+

    Output of Listing 3.94 InnoDB clusters rows by primary key. In one run here, 300,000 inserts took 2.43 s and 36 MB with INT AUTO_INCREMENT, 2.51 s and 40 MB with swapped UUIDs, and 5.46 s and 60 MB with RANDOM_BYTES(16), standing in for random version 4 UUIDs. RFC 9562 (2024) defines time-ordered version 7; MySQL 9.7 cannot generate it (MariaDB 11.7 can), so make v7 in PHP and store it with UUID_TO_BIN(u), without the swap.

    3.10.9 Denormalization and When It Pays

    Denormalization stores a derived or copied value on purpose to make a measured, hot read cheap. The classic case is an order total, which shop computes from order_items. At 200,000 orders:

    SET SESSION cte_max_recursion_depth = 200000;

    CREATE TABLE big_orders (PRIMARY KEY (id)) AS WITH RECURSIVE n (id) AS

    (SELECT 1 UNION ALL SELECT id + 1 FROM n WHERE id < 200000) SELECT id FROM n;

    CREATE TABLE big_items (PRIMARY KEY (order_id, product_id)) AS

    SELECT o.id AS order_id, p.id AS product_id, (o.id * p.id) % 4 + 1 AS quantity,

    5 + (o.id * 7 + p.id) % 45 AS unit_price

    FROM big_orders AS o JOIN (VALUES ROW(1), ROW(4), ROW(7)) AS p (id);

    SET @t = NOW(6);

    SELECT GROUP_CONCAT(order_id) INTO @a FROM (SELECT order_id, SUM(quantity * unit_price) AS t

    FROM big_items GROUP BY order_id ORDER BY t DESC, order_id DESC LIMIT 10) AS x;

    SET @agg = TIMESTAMPDIFF(MICROSECOND, @t, NOW(6)) / 1e6;

    ALTER TABLE big_orders ADD total DECIMAL(10,2) NOT NULL DEFAULT 0, ADD INDEX (total);

    UPDATE big_orders AS o JOIN (SELECT order_id, SUM(quantity * unit_price) AS s FROM big_items

    GROUP BY order_id) AS t ON t.order_id = o.id SET o.total = t.s;

    SET @t = NOW(6);

    SELECT GROUP_CONCAT(id) INTO @b FROM

    (SELECT id FROM big_orders ORDER BY total DESC, id DESC LIMIT 10) AS x;

    SELECT @agg AS aggregate_s, TIMESTAMPDIFF(MICROSECOND, @t, NOW(6)) / 1e6 AS stored_s,

    @a = @b AS same_top_ten;

    Listing 3.95: The ten largest orders: aggregating the lines versus a stored, indexed column

    +-------------+----------+--------------+

    | aggregate_s | stored_s | same_top_ten |

    +-------------+----------+--------------+

    | 0.319663 | 0.000616 | 1 |

    +-------------+----------+--------------+

    Output of Listing 3.95 A reverse scan of the index answers hundreds of times faster. Keep sort directions equal: with ORDER BY total DESC, id the index was useless and the query took 0.80 s. The price is upkeep. An AFTER INSERT trigger adding NEW.quantity * NEW.unit_price kept total correct in a test, but needs UPDATE and DELETE twins (Section 3.15.5); or write the total in the same PHP transaction as the lines. order_items.unit_price is no copy: it is the price at the time of sale. MySQL 9.7 accepts CREATE MATERIALIZED VIEW but makes an ordinary view; only HeatWave materializes. Denormalize when reads dominate, the cost is measured, and one mechanism owns the copy.