Database modelling
Normalizing a database
Our database stores customers, orders, order items, and products in separate tables.
But imagine storing all of that information in a single table:
12345678order_detailsorder_id | customer_id | customer_name | customer_email | product_id | product_name | quantity---------+-------------+---------------+-----------------------+------------+--------------+---------101 | 7 | Customer 7 | Customer7@Example.com | 10 | Product 10 | 2101 | 7 | Customer 7 | Customer7@Example.com | 25 | Product 25 | 1102 | 7 | Customer 7 | Customer7@Example.com | 10 | Product 10 | 1103 | 9 | Customer 9 | Customer9@Example.com | 10 | Product 10 | 3At first, this might seem convenient because everything is available in one place.
But notice how much information is repeated.
Customer 7 appears several times.
Product 10 also appears several times.
As the database grows, this duplication creates problems.
Repeated data
Suppose Customer 7 places hundreds of orders.
If the customer's name and email are stored alongside every order item, the same information is copied again and again.
The same problem exists for products. If Product 10 appears in thousands of orders, its name might also be repeated thousands of times.
The more important problem is not simply the extra storage.
It is that the same fact now exists in many places.
Updating repeated data
Imagine Customer 7 changes their email address.
If the email is stored in many rows, all of those copies need to be updated.
If some are missed, we could end up with:
12345customer_id | customer_email------------+-------------------------7 | NewEmail@Example.com7 | Customer7@Example.com7 | NewEmail@Example.comThe database now contains conflicting versions of the same fact.
Separating customer information
Instead of repeating the customer's details in every order row, we can store each customer once:
123456customersid | full_name | email---+------------+-----------------------7 | Customer 7 | Customer7@Example.com9 | Customer 9 | Customer9@Example.comThe orders table then stores the customer ID:
1234567ordersid | customer_id----+------------101 | 7102 | 7103 | 9The reference is:
1orders.customer_id → customers.idNow the customer's email has one clear place to live:
1customers.emailIf it changes, we update one customer row.
Separating product information
We can apply the same idea to products.
Instead of repeating the product name in every order, store each product once:
123456productsid | name---+-----------10 | Product 1025 | Product 25The product can then be referenced by its ID.
Separating order items
One order can contain multiple products, so the relationship between orders and products belongs in its own table:
12345678order_itemsorder_id | product_id | quantity---------+------------+---------101 | 10 | 2101 | 25 | 1102 | 10 | 1103 | 10 | 3The resulting structure looks like this:
What is normalization?
Normalization is the process of organizing data so that different kinds of facts are stored in appropriate places and unnecessary duplication is reduced.
In our database:
1234567891011customers→ information about customersorders→ information about ordersproducts→ information about productsorder_items→ information about a product within an orderA customer's email belongs in customers.
A product's name belongs in products.
The quantity of a particular product in a particular order belongs in order_items.
Each fact has a clear owner.
Update problems
Separating the data helps prevent update problems.
If a customer's email is stored once in customers, changing it requires one update.
If it were copied across hundreds of order rows, every copy would need to be kept synchronized.
Insertion problems
Imagine product information existed only inside order rows.
How would we add a new product before anyone had ordered it?
With a separate products table, we can create the product independently of any order.
The existence of a product does not depend on the existence of an order containing it.
Deletion problems
Now imagine product information existed only inside order rows.
If we deleted the last order containing a product, we might also accidentally remove the only record of that product.
Keeping products in their own table prevents the lifetime of one kind of data from depending unnecessarily on another.
These kinds of problems are commonly described as update, insertion, and deletion anomalies.
Normalization does not mean avoiding repeated values
Consider:
123456ordersid customer_id101 7102 7103 7The value 7 appears several times.
That is not problematic duplication.
Those rows represent three different orders that were genuinely placed by the same customer.
What we want to avoid is unnecessarily repeating facts such as Customer 7 and Customer7@Example.com inside every one of those order rows.
The repeated foreign key represents a relationship.
Repeated customer details would represent duplicated facts.
A useful design question
When deciding where a column belongs, ask:
What does this value describe?
For example:
1234567891011email→ describes a customerstatus→ describes an ordername→ describes a productquantity→ describes a product within a particular orderAnother useful question is:
Where should the authoritative copy of this fact live?
A normalized design tries to give each fact a clear place to live rather than requiring the same fact to be maintained in many different rows.
In the next chapter, you will see why deliberately storing additional or duplicated information can sometimes still be useful.