DEV Community

-- WRONG: orders copies the user's name and email into every row
orders
+----+-----------+-------------------+--------+
| id | user_name | user_email | total |
+----+-----------+-------------------+--------+
| 10 | Sara | sara@old.com | 50.00 |
| 11 | Sara | sara@old.com | 20.00 |
| 12 | Sara | sara@old.com | 99.00 |
+----+-----------+-------------------+--------+

Looks fine, until Sara changes her email to sara@new.com. Now you have to update it in every single order row. Miss one, and your data lies to you. You will have three rows saying old.com and pretend that is still Sara.

This bug has a name: an update anomaly. The same fact lives in many places, so one fact can fall out of sync with itself.

The fix is the address book idea. Store Sara once, and point to her:

-- RIGHT: store the user once, point by id
users
+----+------+----------------+
| id | name | email |
+----+------+----------------+
| 1 | Sara | sara@new.com | <-- change it here, ONE time
+----+------+----------------+
orders
+----+---------+--------+
| id | user_id | total |
+----+---------+--------+
| 10 | 1 | 50.00 | <-- just points to Sara
| 11 | 1 | 20.00 |
| 12 | 1 | 99.00 |
+----+---------+--------+

Sara changes her email? You update one row in users. Every order is instantly correct, because no order stored a copy in the first place. One source of truth.

(You may hear the fancy names for the rules behind this: 1NF, 2NF, 3NF. They exist, you can read them later, but the plain idea "do not repeat the same fact" gets you 90% of the way.)

The full schema, all together

Here is how our four tables connect:

users
 | (one-to-many: user_id on orders)
 v
orders
 | (many-to-many through order_items)
 v
order_items -----> products

Read it as: a user has many orders, and an order links to many products through order_items.

Gotchas

Over-normalizing everything. You do not need a separate table for every tiny thing. A country column on users is usually fine as plain text. Do not build a 6-table maze for data that never changes and is never reused.
A little duplication is sometimes okay. Storing the product's price at the time of sale inside order_items is smart, not a sin. Prices change, but an old order should keep the price the customer actually paid. The test is: is this a snapshot (keep it) or a live fact (point to it)?
Forgetting the foreign key constraint. Pointing by id only helps if the id is real. Add a FOREIGN KEY so the database refuses orphan rows (an order pointing to a user that does not exist).

Recap

One-to-many: foreign key on the many side (orders.user_id).
Many-to-many: a join table in the middle (order_items).
One-to-one: like one-to-many, but the key is UNIQUE.
Normalization: store each fact once, point to it by id, change it in one spot.
Copying the same data everywhere causes update anomalies (stale, conflicting copies).
Do not overdo it. Snapshots like the sale price are fine to store.

Your turn

You are building a blog. A post can have many tags, and a tag can be on many posts. What three tables do you need, and which one is the join table? If you can draw the little arrow diagram for a friend, you have got it.

Next up: Part 10: Subqueries and CTEs, where we put a query inside a query and then clean it all up with a tidy WITH block.

Top comments (1)

edriso profile image

Mohamed Idris

Hello, world!

Email

edriso.dev@gmail.com
Education

BCS @ Alexandria University
Work

Web Developer
Joined

Dec 17, 2020

• May 29

Part 09 Practice: Relationships and Normalization

Time to design and spot problems like a pro. These use our shared schema: users, orders, products, order_items. Try each one before peeking at the solution.

1. Spot the foreign key

In a one-to-many between users and orders, which table gets the foreign key column, and what is it called?

Solution

-- The "many" side gets it. Orders is the many side (one user, many orders).
-- So orders gets a user_id column pointing back to users.id
CREATE TABLE orders (
 id INT PRIMARY KEY,
 user_id INT, -- the foreign key
 total DECIMAL(10,2),
 status VARCHAR(20),
 created_at TIMESTAMP,
 FOREIGN KEY (user_id) REFERENCES users(id)
);

Why: the foreign key always lives on the "many" side, because each of the many rows needs to remember its one parent.

2. Design a join table

An order can have many products, and a product can be in many orders. Write the CREATE TABLE for the join table that makes this work.

Solution

CREATE TABLE order_items (
 id INT PRIMARY KEY,
 order_id INT,
 product_id INT,
 quantity INT,
 FOREIGN KEY (order_id) REFERENCES orders(id),
 FOREIGN KEY (product_id) REFERENCES products(id)
);

Why: a many-to-many needs a table in the middle. Each row links one order to one product. Two foreign keys, one to each side.

3. Spot the duplication problem

A teammate built this table. What is wrong with it, and what is the bug called?

orders
+----+-----------+----------------+--------+
| id | user_name | user_email | total |
+----+-----------+----------------+--------+
| 10 | Sara | sara@mail.com | 50.00 |
| 11 | Sara | sara@mail.com | 20.00 |
+----+-----------+----------------+--------+

Solution

-- The user's name and email are copied into every order row.
-- When Sara changes her email, you must update many rows, and
-- if you miss one your data goes out of sync. This is an UPDATE ANOMALY.
-- Fix: store the user once in users, and point by id.
orders
+----+---------+--------+
| id | user_id | total |
+----+---------+--------+
| 10 | 1 | 50.00 |
| 11 | 1 | 20.00 |
+----+---------+--------+

Why: the same fact (Sara's email) is stored in many places. One source of truth means you change it once and everything stays correct.

4. One-to-one design

You want to store each user's bio and avatar_url in a separate user_profiles table, with exactly one profile per user. Write the table so a user cannot have two profiles.

Solution

CREATE TABLE user_profiles (
 id INT PRIMARY KEY,
 user_id INT UNIQUE, -- UNIQUE makes it one-to-one
 bio TEXT,
 avatar_url VARCHAR(255),
 FOREIGN KEY (user_id) REFERENCES users(id)
);

Why: a one-to-one is just a one-to-many with a UNIQUE on the foreign key. The UNIQUE blocks a second profile for the same user.

5. Snapshot vs live fact

Should order_items store the product's price at the time of sale, or should it always read the current price from products? Explain.

Solution

-- Store a snapshot of the price at sale time:
CREATE TABLE order_items (
 id INT PRIMARY KEY,
 order_id INT,
 product_id INT,
 quantity INT,
 unit_price DECIMAL(10,2), -- the price the customer actually paid
 FOREIGN KEY (order_id) REFERENCES orders(id),
 FOREIGN KEY (product_id) REFERENCES products(id)
);

Why: prices change over time. If you always read the current price, an old receipt would change whenever you update the product. A past sale is a snapshot, so a little "duplication" here is correct, not a mistake.

6. Draw the relationships

Without writing SQL, name the relationship type between each pair, and where the link lives:
(a) users and orders, (b) orders and products, (c) users and user_profiles.

Solution

(a) users <-> orders : one-to-many (foreign key user_id on orders)
(b) orders <-> products : many-to-many (join table order_items in the middle)
(c) users <-> user_profiles : one-to-one (UNIQUE foreign key user_id on profiles)

Why: knowing the shape tells you exactly where the key goes. Many-to-many is the only one that needs a helper table.

Nice work. If you can look at any two tables and instantly say "one-to-many, key goes here" or "many-to-many, needs a join table," you are thinking like a database designer. See you in Part 10.