MENU


Design and Normalization

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 Defining Tables. Rebuild it by flattening the sample data, adding an authors cell the way people type it:

The spreadsheet the shop kept before it had a databaseSQL
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);
Output
+----------+-------------+-----------+-------------+-------+-----+------------------------+
| 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 |
+----------+-------------+-----------+-------------+-------+-----+------------------------+

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:

Update, insert and delete anomalies in the sheetSQL
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;
Output
ERROR 1364 (HY000): Field 'email' doesn't have a default value
+-----------+------+
| ben_names | dara |
+-----------+------+
|         2 |    0 |
+-----------+------+

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.

Subsections