Structure your schemas from 1NF to 3NF to eliminate anomalies, and know when to denormalize with full awareness.
Open this lesson in KodokonA bad schema costs you for years. Normalization aims at one thing: that each fact is stored only once, to eliminate three anomalies: update (changing one email in ten places), insertion (unable to add a product without an order), and deletion (deleting the last order erases the customer). Look at this deliberately botched schema.
CREATE TABLE bad_orders (
id INTEGER PRIMARY KEY,
customer_name TEXT,
customer_email TEXT,
product_names TEXT,
total REAL
);
INSERT INTO bad_orders
(customer_name, customer_email,
product_names, total)
VALUES
('Alice', 'alice@mail.com',
'keyboard,mouse', 74.0),
('Alice', 'alice@mail.co',
'monitor', 199.0);The first three normal forms, in practice. 1NF: atomic values - product_names = 'keyboard,mouse' violates it, making any WHERE or JOIN on products impractical. 2NF (composite key): every column depends on the whole key - storing a product's catalog price in an order line identified by (order, product) violates it, because the price depends only on the product. 3NF: no non-key column depends on another non-key column - customer_email, which depends on customer_name, violates it; hence the alice@mail.co duplicate. Here is the fixed schema.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
);
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price REAL NOT NULL
);
CREATE TABLE purchases (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL
REFERENCES customers(id)
);
CREATE TABLE purchase_items (
purchase_id INTEGER NOT NULL
REFERENCES purchases(id),
product_id INTEGER NOT NULL
REFERENCES products(id),
quantity INTEGER NOT NULL,
PRIMARY KEY (purchase_id, product_id)
);PRAGMA foreign_keys = ON;
INSERT INTO customers (name, email)
VALUES ('Alice', 'alice@mail.com');
INSERT INTO products (name, price)
VALUES ('keyboard', 49.0), ('mouse', 25.0);
INSERT INTO purchases (customer_id) VALUES (1);
INSERT INTO purchase_items
(purchase_id, product_id, quantity)
VALUES (1, 1, 1), (1, 2, 1);
SELECT c.name, p.name AS product,
i.quantity, p.price
FROM purchase_items AS i
JOIN purchases AS pu ON pu.id = i.purchase_id
JOIN customers AS c ON c.id = pu.customer_id
JOIN products AS p ON p.id = i.product_id;So, when should you denormalize? When reads become the measured bottleneck: five-table joins on a critical screen, an aggregate recomputed on every render. The honest patterns: a cache column (purchases.total frozen at order time - and legitimately so: the price paid is a historical fact, distinct from the catalog price), a counter maintained by a trigger, or a reporting table rebuilt in batch. The price to pay: each copy can drift, and it is now your code, not the engine, that guarantees consistency.