One Flat Sheet, Two Addresses for the Same Phone Number

Symptom 1 is an update anomaly: customer attributes depend only on the customer, but they are stored once per order, so one fact has 14 copies and a partial update creates contradiction. Symptom 2 is a data integrity/domain problem: no controlled vocabulary, so the same shelf has three spellings; grouping reports split the total three ways. Symptom 3 and 4 together are insert/delete anomalies: the delete of the last order deletes the only record of the product (delete anomaly); a new product cannot be recorded until someone orders it (insert anomaly); and the lack of a stable product key is why it gets re-typed with a new spelling. Symptom 5 is a consequence: wide rows with repeated long text mean more pages read.

Schema

customer(cust_id PK, name, phone UNIQUE, address)
product(product_id PK, name, category_id FK, current_price)
category(category_id PK, name UNIQUE)
orders(order_id PK, order_date, cust_id FK, salesman_id FK)
order_line(order_id FK, product_id FK, qty, unit_price, PRIMARY KEY(order_id, product_id))
salesman(salesman_id PK, name)

order_line is in 1NF (atomic values, no repeating groups), 2NF (every non-key attribute depends on the whole composite key - qty and unit_price do), and 3NF (no non-key attribute depends on another non-key attribute). One spelling per category is enforced by the UNIQUE constraint, and a price change is now one UPDATE on product, not 14.

order_line.unit_price is repeated and that is deliberate. It is the price charged at the time of sale, not the product's current price, so it depends on the line (order + product) and nothing else. Copying product.current_price into it is not redundancy - it is the historical fact the owner wants to keep. Reading the live price instead would silently rewrite the value of every past order when a price changes.

-- Top 5 by revenue, last 3 months
SELECT p.name,
       SUM(ol.qty * ol.unit_price) AS revenue
FROM order_line ol
JOIN orders  o ON o.order_id  = ol.order_id
JOIN product p ON p.product_id = ol.product_id
WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
GROUP BY p.product_id, p.name
ORDER BY revenue DESC
LIMIT 5;

The revenue must be computed from qty * unit_price on the line, not from product.current_price, for the same reason as above - a price rise would otherwise inflate last quarter's figures. The WHERE filters rows before grouping; putting the date test in HAVING would filter after summing and give wrong totals. p.product_id is grouped as well as p.name so two products that share a name are not merged.

-- Customers with > 3 orders and lifetime spend > 5000
SELECT c.cust_id, c.name,
       COUNT(DISTINCT o.order_id) AS orders,
       SUM(ol.qty * ol.unit_price) AS total
FROM customer c
JOIN orders     o  ON o.cust_id     = c.cust_id
JOIN order_line ol ON ol.order_id   = o.order_id
GROUP BY c.cust_id, c.name
HAVING COUNT(DISTINCT o.order_id) > 3
   AND SUM(ol.qty * ol.unit_price) > 5000;

HAVING is right here because both conditions are aggregates over the group; WHERE cannot see COUNT/SUM. DISTINCT in the count is needed because the join multiplies rows - a customer with 2 orders of 3 lines each produces 6 rows, and COUNT(*) would wrongly say 6 orders.

Index every column is the wrong move: each extra index costs disk and slows every INSERT and UPDATE, because all of them must be maintained. And several of the requested indexes cannot be used by the optimiser anyway.

What helps:

1. orders(order_date) - the range filter >= DATE_SUB(...) is the selective condition; this lets the optimiser discard old rows before the join.
2. orders(cust_id, order_date) as a composite - leftmost-prefix rule: it serves both WHERE cust_id = ? and WHERE cust_id = ? AND order_date BETWEEN ..., but not WHERE order_date = ? alone. Order of columns matters; put the equality column first.
3. order_line(order_id, product_id) is already the primary key of the composite, and InnoDB indexes it - this is what makes the join to orders cheap.
4. product(category_id) for shelf-wise reports - a foreign key column should be indexed anyway.

Where an index will not help:

• SELECT ... WHERE product_name LIKE '%lamp%' - a leading wildcard has no left anchor, so a B-tree cannot seek; it degrades to a full scan. LIKE 'lamp%' can use the index.
• Any predicate wrapped in a function, e.g. WHERE LOWER(cust_name) = 'meera' or WHERE YEAR(order_date) = 2024, unless a functional index is created - the column is no longer referenced directly.
• A low-selectivity column such as a gender flag returns most of the table, so the optimiser will often ignore the index and scan anyway.
• SUM(qty) is never served by the column index; the value has to be read.

The real fix for symptom 5 is the normalisation itself: sum a narrow integer qty and unit_price instead of scanning repeated 60-character address and product strings. Then run EXPLAIN on both queries to confirm the intended index is used rather than assuming it.