Database Design: Keys, ER Diagrams and Normalization

Every query you have written so far relied on a schema someone designed well: one row per customer, prices in one place, a junction table for order lines. This page is about how that design is made. Bad design does not show up as an error message. It shows up months later as a customer with two cities, a revenue report that double-counts, or a training dataset whose labels quietly disagree with each other.

The tool for good design is normalization: storing each fact exactly once, so it cannot contradict itself. You will take a messy, spreadsheet-style export, find its problems with SQL, and normalise it step by step — and then see when analytics and ML deliberately denormalise again.

What you will learn

• Go from requirements to entities, attributes and relationships, and draw them as a text ER diagram
• Candidate, composite, natural and surrogate keys, and referential integrity
• Functional dependencies, and how to test one against real data with SQL
• 1NF, 2NF, 3NF and BCNF on a messy table, step by step
• Junction tables, and when to denormalise for analytics

From requirements to entities

Design starts with sentences, not tables. Suppose the shop's requirements say:

Customers place orders. An order has a date and a status and contains one or more products, each with a quantity and the price actually charged. Products belong to at most one category. An order can be paid in several payments.

A useful first pass: nouns that have their own identity become entities (customer, order, product, category, payment); nouns that only describe something become attributes (date, status, quantity); verbs become relationships (places, contains, belongs to, pays). For each relationship ask "how many on each side?" — that decides where the foreign key goes. "Contains" is many-to-many (an order has many products, a product is in many orders), and "quantity" and "price charged" belong to neither side alone: they describe the pairing. That is why order_items exists.

An ER diagram in text

An entity-relationship (ER) diagram shows the entities and how they connect. You do not need a drawing tool; this crow's-foot notation (the syntax Mermaid's erDiagram renders, so it also works in GitHub README files) is enough:

CUSTOMERS   ||--o{ ORDERS      : places          one customer, zero or more orders
ORDERS      ||--|{ ORDER_ITEMS : contains        one order, one or more lines
PRODUCTS    ||--o{ ORDER_ITEMS : "sold as"       one product, zero or more lines
CATEGORIES  |o--o{ PRODUCTS    : groups          a product has zero or one category
ORDERS      ||--o{ PAYMENTS    : "paid by"       an order, zero or more payments
EMPLOYEES   |o--o{ EMPLOYEES   : manages         a self-relationship

||  exactly one     |o  zero or one     |{  one or more     o{  zero or more

Reading the symbols near each table tells you the business rules: an order must have exactly one customer (customer_id NOT NULL), but a product may have no category (category_id nullable — product 11, the Gift Card).

Keys

Natural keys change (people change email addresses) and leak into other tables when used as foreign keys, so most systems use a surrogate primary key and a UNIQUE constraint on the natural key — exactly what customers does. Junction tables are the exception: their natural composite key (order_id, product_id) is ideal, because it also forbids the same product appearing twice in one order.

Referential integrity

A foreign key promises that every reference points at a row that exists. With foreign keys switched on, the database keeps that promise for you:

INSERT INTO orders (order_id, customer_id, order_date, status)
VALUES (99, 42, '2026-07-01', 'pending');

Error: FOREIGN KEY constraint failed

There is no customer 42, so the insert is refused. Without the constraint you would have an "orphan" order that silently disappears from every INNER JOIN report. What happens when a referenced row is deleted (refuse, cascade, set to NULL) is covered in Transactions, ACID, Isolation and Locking.

A messy table

Here is the kind of table that arrives as a spreadsheet export: every order line with all its details copied in. We build it from the shop for customers 1 and 2:

CREATE TABLE order_sheet AS
SELECT o.order_id, o.order_date,
       c.email AS customer_email, c.name AS customer_name, c.city AS customer_city,
       p.name AS product, cat.name AS category, p.price AS list_price,
       oi.quantity, oi.unit_price
FROM orders AS o
JOIN customers   AS c   ON c.customer_id = o.customer_id
JOIN order_items AS oi  ON oi.order_id = o.order_id
JOIN products    AS p   ON p.product_id = oi.product_id
JOIN categories  AS cat ON cat.category_id = p.category_id
WHERE o.customer_id IN (1, 2);

SELECT order_id, order_date, customer_name, customer_city, product, category, quantity
FROM order_sheet
ORDER BY order_id, product;

+----------+------------+---------------+---------------+-------------------------+-------------+----------+
| order_id | order_date | customer_name | customer_city | product                 | category    | quantity |
+----------+------------+---------------+---------------+-------------------------+-------------+----------+
|        1 | 2026-01-12 | Nadia Rahman  | Dhaka         | Python Crash Course     | Books       |        1 |
|        1 | 2026-01-12 | Nadia Rahman  | Dhaka         | Wireless Mouse          | Electronics |        1 |
|        2 | 2026-01-20 | Tanvir Ahmed  | Chattogram    | Mechanical Keyboard     | Electronics |        1 |
|        3 | 2026-02-03 | Nadia Rahman  | Dhaka         | SQL Masterclass         | Courses     |        1 |
|        6 | 2026-03-08 | Tanvir Ahmed  | Chattogram    | AI Engineering Bootcamp | Courses     |        1 |
|       10 | 2026-04-19 | Nadia Rahman  | Dhaka         | 27-inch Monitor         | Electronics |        1 |
|       12 | 2026-05-21 | Tanvir Ahmed  | Chattogram    | Laptop Stand            | Accessories |        2 |
+----------+------------+---------------+---------------+-------------------------+-------------+----------+

It is easy to read and easy to load into pandas. It is also a trap.

Three anomalies

• Update anomaly: Tanvir's city is stored three times. Change one copy and the data contradicts itself.
• Insertion anomaly: you cannot record a new product until somebody orders it — there is no row to put it in.
• Deletion anomaly: delete Tanvir's three orders (say, for a data-retention policy) and you also lose the fact that he is a customer from Chattogram.

Watch the first one happen. Tanvir moves to Dhaka; the support tool updates only the order it had open:

UPDATE order_sheet SET customer_city = 'Dhaka' WHERE order_id = 12;

SELECT customer_email, COUNT(DISTINCT customer_city) AS cities,
       GROUP_CONCAT(DISTINCT customer_city) AS which
FROM order_sheet
GROUP BY customer_email
ORDER BY customer_email;

+--------------------+--------+------------------+
| customer_email     | cities | which            |
+--------------------+--------+------------------+
| nadia@example.com  |      1 | Dhaka            |
| tanvir@example.com |      2 | Chattogram,Dhaka |
+--------------------+--------+------------------+

A model trained on this table would see Tanvir in two cities at once. A "revenue by city" report would split him in half.

Functional dependencies

A functional dependency A → B ("A determines B") means: rows with the same A always have the same B. The design rules below are all about dependencies, so list them first. For order_sheet, whose key is (order_id, product):

order_id            → order_date, customer_email
customer_email      → customer_name, customer_city
product             → category, list_price
(order_id, product) → quantity, unit_price

Dependencies come from the business rules, but you can test one against data: group by the left side and count distinct values of the right side. Any group with more than one value breaks the rule — just what the query above found for customer_email → customer_city. This is a quick data-quality check worth running on any dataset before training on it:

SELECT product, COUNT(DISTINCT list_price) AS prices, COUNT(DISTINCT unit_price) AS charged
FROM order_sheet
GROUP BY product
HAVING COUNT(DISTINCT unit_price) > 1 OR COUNT(DISTINCT list_price) > 1;

+---------+--------+---------+
| product | prices | charged |
+---------+--------+---------+
+---------+--------+---------+

No rows: product → list_price holds here, and unit_price also happens to be constant. But we know from order 9 that the price charged can differ from the list price (a discount), so unit_price depends on the order line, not on the product. Data can only show that a dependency is broken; whether it should hold is a business question.

Normal forms, step by step

1NF: atomic values, no repeating groups

A table is in first normal form when every cell holds one value and there are no repeating columns like product1, product2. The original export had a cell like "Python Crash Course, Wireless Mouse" for order 1; giving each product its own row (as order_sheet does) fixes that. You cannot JOIN, GROUP BY or index the inside of a comma-separated string, which is why 1NF matters even for "just analytics".

2NF: no partial dependencies

Second normal form: 1NF, and no non-key column (one that is not part of any candidate key) depends on only part of a composite key. A table whose key is a single column is automatically in 2NF; the problem only appears with composite keys like (order_id, product). order_date and customer_email depend on order_id alone; category and list_price on product alone. Move each group into a table keyed by the part it depends on:

order_sheet(order_id, product, order_date, customer_email, customer_name, customer_city,
            category, list_price, quantity, unit_price)
  ↓ split by what each column depends on
orders(order_id PK, order_date, customer_email, customer_name, customer_city)
products(product PK, category, list_price)
order_items(order_id, product, quantity, unit_price)   PK (order_id, product)

3NF: no transitive dependencies

Third normal form: 2NF, and no non-key column depends on the key only through another non-key column (a transitive dependency: key → X → Y). In the new orders, customer_name and customer_city depend on customer_email, which depends on order_id: a transitive chain. Split again — and the same for category, which deserves its own table so that categories with no products (Furniture) can exist:

customers(customer_id PK, email UNIQUE, name, city)
orders(order_id PK, customer_id FK, order_date, status)
categories(category_id PK, name UNIQUE)
products(product_id PK, name, category_id FK, price, stock)
order_items(order_id FK, product_id FK, quantity, unit_price)   PK (order_id, product_id)

That is the shop schema you have used all along. The short version of the rule: every non-key column depends on the key, the whole key, and nothing but the key. And the split loses nothing — joining the pieces gives back the original rows:

SELECT COUNT(*) AS rebuilt_rows
FROM orders AS o
JOIN customers   AS c   ON c.customer_id = o.customer_id
JOIN order_items AS oi  ON oi.order_id = o.order_id
JOIN products    AS p   ON p.product_id = oi.product_id
JOIN categories  AS cat ON cat.category_id = p.category_id
WHERE o.customer_id IN (1, 2);

+--------------+
| rebuilt_rows |
+--------------+
|            7 |
+--------------+

Seven rows, as in order_sheet. A decomposition that can be joined back exactly is called lossless. Splitting along a functional dependency, so that the shared column is the key of one of the pieces (as customer_id is for customers), guarantees it.

BCNF: every determinant is a key

Boyce–Codd normal form tightens 3NF: for every dependency A → B, A must be a candidate key. Tables in 3NF but not BCNF are rare, but here is the classic one. Students are assigned a mentor per course, and each mentor mentors exactly one course:

CREATE TABLE mentoring (
    student TEXT NOT NULL,
    course  TEXT NOT NULL,
    mentor  TEXT NOT NULL,
    PRIMARY KEY (student, course)
);
INSERT INTO mentoring VALUES
    ('Nadia',  'SQL', 'Rina'),
    ('Tanvir', 'SQL', 'Rina'),
    ('Nadia',  'ML',  'Joy'),
    ('Mitu',   'ML',  'Sami');

UPDATE mentoring SET course = 'Python' WHERE mentor = 'Rina' AND student = 'Nadia';

SELECT mentor, GROUP_CONCAT(DISTINCT course) AS courses
FROM mentoring
GROUP BY mentor
ORDER BY mentor;

+--------+------------+
| mentor | courses    |
+--------+------------+
| Joy    | ML         |
| Rina   | Python,SQL |
| Sami   | ML         |
+--------+------------+

mentor → course holds by rule, but mentor is not a key, so the table stores "Rina teaches SQL" twice — and one update made Rina a mentor of two courses. Every column here is part of some candidate key, so the table is in 3NF; it fails BCNF. The fix is mentors(mentor PK, course) and student_mentors(student, mentor). The honest trade-off: after the split, "one mentor per student per course" can no longer be enforced by a simple primary key. Normal forms are tools, not laws.

Junction tables

A many-to-many relationship always becomes a junction table holding two foreign keys, usually as a composite primary key, plus any facts about the pairing. Tags on products — useful later as metadata filters in search — are a typical case:

CREATE TABLE product_tags (
    product_id INTEGER NOT NULL,
    tag        TEXT    NOT NULL,
    PRIMARY KEY (product_id, tag),
    FOREIGN KEY (product_id) REFERENCES products (product_id)
);
INSERT INTO product_tags VALUES (1, 'python'), (2, 'ml'), (2, 'python'), (7, 'ml'), (6, 'sql');

SELECT p.name, GROUP_CONCAT(t.tag, ', ') AS tags
FROM products AS p
JOIN product_tags AS t ON t.product_id = p.product_id
GROUP BY p.product_id, p.name
ORDER BY p.product_id;

INSERT INTO product_tags VALUES (2, 'ml');

+---------------------------+------------+
| name                      | tags       |
+---------------------------+------------+
| Python Crash Course       | python     |
| Hands-On Machine Learning | ml, python |
| SQL Masterclass           | sql        |
| AI Engineering Bootcamp   | ml         |
+---------------------------+------------+
Error: UNIQUE constraint failed: product_tags.product_id, product_tags.tag

The composite key does real work: the duplicate tag is rejected. Storing tags as a string column ('ml,python') would break 1NF and make "all products tagged ml" a fragile LIKE query.

Denormalization: when analytics wants it flat

Normalized tables are right for the system that records data: every write touches one place. Analytics and ML read far more than they write, and they want one wide row per thing — a pandas DataFrame, a feature table, a BI dataset. So we deliberately denormalise for them, by building the flat shape from the normalized source:

CREATE VIEW sales_wide AS
SELECT o.order_id, o.order_date, c.city, cat.name AS category, p.name AS product,
       oi.quantity * oi.unit_price AS revenue
FROM orders AS o
JOIN customers   AS c   ON c.customer_id = o.customer_id
JOIN order_items AS oi  ON oi.order_id = o.order_id
JOIN products    AS p   ON p.product_id = oi.product_id
LEFT JOIN categories AS cat ON cat.category_id = p.category_id
WHERE o.status <> 'cancelled';

SELECT COALESCE(city, '(none)') AS city, SUM(revenue) AS revenue
FROM sales_wide
GROUP BY city
ORDER BY revenue DESC, city;

+------------+---------+
| city       | revenue |
+------------+---------+
| Dhaka      |   38600 |
| Chattogram |   22500 |
| Khulna     |   19950 |
| (none)     |    4000 |
+------------+---------+

The rule that keeps you safe: denormalise copies, never the source of truth. A view (always current) or a table rebuilt by a pipeline (fast, but a snapshot) — and the star schemas of Warehouses, Lakes, Streams and Scale are this idea done systematically.

Common mistakes

1. Comma-separated lists in a column. tags = 'ml,python' cannot be joined, indexed or constrained. Use a junction table.
2. Copying descriptive columns into child tables "for convenience". orders.customer_city will drift from customers.city. Join, or build a view.
3. Using a natural key as the primary key when it can change. Emails and phone numbers change and are copied into every foreign key. Use a surrogate key plus UNIQUE on the natural one.
4. Forgetting that history is a different fact. order_items.unit_price is not a duplicate of products.price: it is the price at the time of sale. Normalizing it away would rewrite every past order when the price changes.
5. Normalizing the analytics layer to death. A feature table that needs nine joins per training run is slow and error-prone. Keep the source normalized and give analytics a flat, rebuildable copy.

Try it yourself

1. Easy: In order_sheet, check whether order_id → order_date holds (list any order with more than one date).
2. Medium: Write the functional dependencies of a table enrolments(student_email, student_name, course_code, course_title, enrolled_on, score) with key (student_email, course_code), then name the 3NF tables.
3. Hard: Fix the mentoring table: create mentors and student_mentors, fill them from the original four rows' rules (Rina→SQL, Joy→ML, Sami→ML), and show each student's course and mentor by joining them.

Answers

-- Easy: no rows means the dependency holds
SELECT order_id, COUNT(DISTINCT order_date) AS dates
FROM order_sheet
GROUP BY order_id
HAVING COUNT(DISTINCT order_date) > 1;

Medium — the dependencies and the tables:

student_email               → student_name
course_code                 → course_title
(student_email, course_code) → enrolled_on, score
-- 3NF: students(student_email PK, student_name)
--      courses(course_code PK, course_title)
--      enrolments(student_email FK, course_code FK, enrolled_on, score) PK (student_email, course_code)

-- Hard
CREATE TABLE mentors (mentor TEXT PRIMARY KEY, course TEXT NOT NULL);
CREATE TABLE student_mentors (
    student TEXT NOT NULL,
    mentor  TEXT NOT NULL,
    PRIMARY KEY (student, mentor),
    FOREIGN KEY (mentor) REFERENCES mentors (mentor)
);
INSERT INTO mentors VALUES ('Rina', 'SQL'), ('Joy', 'ML'), ('Sami', 'ML');
INSERT INTO student_mentors VALUES ('Nadia', 'Rina'), ('Tanvir', 'Rina'), ('Nadia', 'Joy'), ('Mitu', 'Sami');

SELECT sm.student, m.course, m.mentor
FROM student_mentors AS sm
JOIN mentors AS m ON m.mentor = sm.mentor
ORDER BY sm.student, m.course;

Summary

• Requirements → entities (nouns with identity), attributes, relationships (verbs); many-to-many needs a junction table.
• Prefer a surrogate primary key plus UNIQUE on the natural key; foreign keys keep references honest.
• Functional dependencies drive normalization, and a GROUP BY … COUNT(DISTINCT …) query tests them on real data.
• 1NF: atomic values. 2NF: no partial dependencies. 3NF: no transitive ones. BCNF: every determinant is a key.
• Keep the source normalized; denormalise copies (views, warehouse tables, training snapshots) for analytics and ML.

Next: Designing a Real Schema, and Changing It Safely applies all of this to a learning-platform database from requirements to DDL, then shows how to change a live schema with versioned migrations.