Here is the starting point, a spreadsheet an operations team in Jaipur keeps for a small online shop. Each row is an order. The columns are: order number, order date, customer name, customer phone, customer pincode, customer city, customer state, a column called “items” that holds entries like “2 x steel bottle, 1 x lunch box”, and a total. We will turn this into a clean relational design in three moves, and notice what breaks at each stage.

What is wrong before we start

Suppose a customer moves house and changes phone number. Every old row for that customer holds the old details, and the new rows hold the new ones. Someone fixing a typo in a city name has to find every row. Delete the only order of a customer and the customer vanishes from the sheet entirely. These are called update, insert and delete anomalies. Normalisation is a set of rules for designing tables so that a fact is stored in one place only, which removes these problems at the root.

Move one: first normal form, one value per cell

The “items” column holds a list inside one cell. You cannot easily count how many steel bottles were sold, or total by product, because you would have to pull text apart. First normal form says each cell holds a single value, and each row is uniquely identifiable. So we create one row per order line: order number, product, quantity, unit price. An order with two products now takes two rows, identified together by order number plus product.

The “total” column is also worth a question. It can be calculated from the lines, so storing it invites disagreement between the stored total and the sum. Many designs keep it out, or keep it deliberately and check it, which is an intentional exception.

Move two: second normal form, depend on the whole key

Now each row is identified by order number and product together. Look at the other columns on that row. Quantity depends on both: how many of this product in this order. But order date depends only on the order number, and the product name and its current list price depend only on the product. These are partial dependencies, parts that rely on half of the key. Repeating the order date on every line of an order wastes space and invites inconsistency.

The fix is to split. An orders table (order number, date, customer) holds facts about the order. An order lines table (order number, product, quantity, price charged) holds line facts. A products table (product, name, category, list price) holds facts about the product. Note that the price actually charged is kept on the line: list prices change, and history must preserve what the customer paid.

Move three: third normal form, nothing but the key

Look at the customer details. In the orders table, customer phone, pincode, city and state depend on the customer, not on the order. That is a dependency through a non-key column. Worse, there is a chain inside it: the pincode determines the city, and the city determines the state. In a Jaipur example, pincode 302001 belongs to one city, which sits in one state, so storing city and state next to every pincode, in every customer row, repeats facts that follow from a single value.

Third normal form says non-key columns should depend on the key, the whole key and nothing but the key. So customer details move to a customers table. Pincode moves to a small reference table that carries city and state. Orders now carry just a customer identifier. Correct a city spelling once, in one row, and every order sees the fix.

The finished design is five small tables: customers, pincodes, orders, order lines and products. Each fact lives once.

When to stop, and when to undo it

Normalisation suits transaction systems, where the same data is written and corrected constantly and consistency matters most. A checkout system should be normalised. Beyond third normal form there are further forms, such as Boyce-Codd, fourth and fifth, which handle rarer dependency patterns. Many real systems stop at third.

Analytics works in the opposite direction. A report that needs revenue by state by product category by month would have to join all five tables, repeatedly, across millions of rows. That is why warehouses use star schemas, which deliberately repeat attributes in wide dimension tables to make reading fast and simple. This is denormalisation as a choice, made after you understand what the normalised version looks like and what repeating the data will cost you. The risk is the same as before: if a customer’s city is copied into many places, you need a reliable load process to keep the copies in step.

A practical test for any table you meet: pick a fact, such as a customer’s city, and ask how many places you would need to edit if it changed. If the answer is more than one, either someone designed it for read speed on purpose, or nobody designed it.

Leave a Reply

Trending