Designing a database well

In a well-designed database every fact is stored in one place. In a badly designed one the same thing is repeated dozens of times and sooner or later contradicts itself. This lesson is about splitting a database the right way.

A bad example

A shop keeps everything in one table:

The problems:

• Repetition — Aziz’s phone is written again in every order. If it changes, it must change everywhere; miss one and the database “knows” two different phones.
• A list in one cell — products holds several products. “How many iPhones were sold?” cannot be answered with SQL.
• A derived value — total follows from the product prices; the two can drift apart.

Normal forms

The rules for structuring a database well are called normal forms. In practice the first three are enough.

1NF — one value per cell. No lists and no repeating columns (phone1, phone2, phone3). Products go on separate rows.

2NF — every column depends on the whole key. If the key has two parts (say order_id + product_id), the product name depends only on product_id — so it belongs in another table (products).

3NF — columns do not depend on each other. The customer’s phone does not belong in an order — it belongs to the customer, not the order. Everything about the customer lives in customers.

The right structure

Just like our shop database:

• customers — customers (name and phone, once);
• products — products (name, price);
• orders — an order: who and when (customer_id);
• order_items — what is in the order: how many of which product (order_id, product_id, quantity).

Now “Aziz’s phone” lives in one place, and “how many iPhones were sold” is a simple query:

SELECT p.name, SUM(oi.quantity) AS sold
FROM order_items oi JOIN products p ON p.id = oi.product_id
WHERE p.brand = 'Apple'
GROUP BY p.name
ORDER BY sold DESC;

Why is the price in order_items too?

products.price is the current price; order_items.unit_price is the price at the moment of sale. That is not repetition: if the price changes tomorrow, the total of a past order must not change. Normalisation is not “never repeat anything”, it is “keep each meaning in one place”.

Denormalisation

Sometimes data is repeated on purpose for speed: for example users.xp is a ready number instead of summing xp_events every time. This must be a deliberate choice with a clear way of keeping the value up to date.

Practical design tips

1. First identify the entities: student, group, subject, grade.
2. Each one is its own table, each with an id.
3. One-to-many — the foreign key goes on the “many” side.
4. Many-to-many — a link table.
5. Clear, consistent column names: created_at, student_id.

Remember

• One meaning, one place: repetition leads to mistakes.
• 1NF — one value per cell; 2NF — depend on the whole key; 3NF — depend only on the key.
• A historical value (the price at the time of sale) is not repetition.