Designing a Relational Database

A small shop keeps its orders in one sheet. Each line has the order number, the date, the customer, their phone, the product, its price and how many were bought. It works fine for a week. Then Ana changes her phone number, someone updates one row and forgets the other two, and the shop no longer knows how to reach her. Nothing was typed wrong. The problem is the design: Ana’s phone is stored in three places, so three places can disagree. Database design is deciding which tables you need, which columns go in each, and how the tables point at each other. The main goal is simple: store each fact once, so it can’t be stored two different ways. This post follows that shop from one sheet to four clean tables. It assumes you know what a table, a row and a key are. If not, read The Relational Model first.

​

One sheet, three kinds of bug

Here is the shop’s sheet. Each row is one order line, so the key is the pair (orderID, product). Try three ordinary changes. These three problems have names: update, delete and insert anomalies. They all have the same cause. The sheet mixes three subjects (customers, products and orders) in one table. Every fix in this post comes down to giving each subject its own table.

​

The four steps

The design process has four steps. You will often loop back, but this is the order to start in.

1. Decide the purpose. What questions must the database answer? Sketch the forms people will fill in and the reports they will read. Anything a report needs must be stored somewhere.
2. Split data into tables and pick primary keys. One table per subject: customers, orders, products. Each table gets a key that picks out exactly one row.
3. Connect the tables. Work out how the subjects relate (one-to-many, many-to-many or one-to-one) and add foreign keys to match.
4. Refine and normalize. Check each table against the normal forms and split tables that still store a fact twice.

The rest of this post walks through steps 2 to 4 with the shop’s data.

​

Picking a primary key

A primary key has two hard rules: its value is unique for each row, and it is never empty (never NULL). There is a third rule, softer but just as important: it should never change. Other tables store copies of the key to point at a row. If the key changes, every copy must change too. Customers has several columns that look unique today. Pick one and watch what normal shop life does to it. customerID wins because it means nothing. It is just a number the database hands out, so no real-world event can make it wrong. A key like this is called a surrogate key. A key made from real data, such as an ISBN or an email address, is called a natural key. Natural keys are fine when an outside authority guarantees they are unique and stable, like an ISBN for a book. When in doubt, use a surrogate. Most databases can generate one for you: AUTO_INCREMENT in MySQL, GENERATED ... AS IDENTITY in PostgreSQL and SQL Server. These use a counter, not “the largest current value plus one”. So after you delete row 7, the next row usually gets 8, not 7 again. Numbers can have gaps, and that is fine; a key only has to be unique. Keep keys small, too. A one-column number is quicker to compare and index than three text columns, and every table that points at it stores a copy.

​

Connecting tables

Once each subject has its own table, you need to record how the subjects relate. There are three shapes. Each one has a naive design that seems to work and a split design that holds up. Try the same change on each. One-to-many is the most common shape. One customer places many orders; one teacher teaches many classes. Put the foreign key on the “many” side: each class row stores its teacher. Never put a list on the “one” side. A row can’t hold a growing list without adding columns. Many-to-many can’t be stored with one foreign key, because neither side has a single partner. A product column in Orders allows one product per order; an orderID column in Products allows one order per product. Either way, the “many” on the other side has nowhere to go. Add a third table, called a junction table, with one row per pair. Its primary key is usually both foreign keys together. It can also hold facts about the pair itself, such as qty. One-to-one is rare. Use it to move columns that are large, often empty or sensitive into their own table, for example product photos or an employee’s salary. The second table uses the same primary key as the first, and that key is also its foreign key.

​

Normalizing, one rule at a time

Normalization is a checklist for finding facts that are still stored twice. Each normal form is one rule. A table in 2NF also follows 1NF, and a table in 3NF follows both. Step through the shop’s data from a raw order list to 3NF. Notice that 1NF made things worse before they got better: Ana’s phone went from two copies to three. 1NF only makes the data queryable. 2NF and 3NF remove the copies. Here are the three rules in plain words:

• First normal form (1NF): one value per cell. No lists in a cell and no item1, item2, item3 columns.
• Second normal form (2NF): each column needs the whole key, not just half of it. Order date needs only the order number, so it doesn’t belong in a table keyed by order and product. This rule only matters when the key has two or more columns.
• Third normal form (3NF): each column is about the key, not about another column. A phone is about the customer, so it doesn’t belong in Orders.

A short way to remember all three: every column describes the key, the whole key, and nothing but the key. Products keeps the product name as its key to keep this example short. A real shop would add a productID for the same reason it added customerID: names get corrected and renamed.

​

Wait, shouldn’t the price stay with the order?

2NF moved price to Products. That is right for today’s price. But if the mug costs 9.00 next month, order 1002 should still show that Ben paid 8.00. “The price Ben paid” is a different fact from “the price of a mug”, and it depends on the whole key (this order, this product). So real systems usually keep both: Products.price for the current price, and OrderDetails.priceAtOrder for history. Normalization doesn’t forbid this. Each column stores a different fact once.

​

Beyond 3NF

3NF has a blind spot: it only checks the columns outside the key. A repeated fact can hide inside the key, and 3NF won’t see it. Boyce–Codd normal form (BCNF) closes the gap with one rule: if one column tells you another, it must be a key. Here is a school that keeps one table of who studies what with whom. It follows two rules:

1. A student takes each course with one teacher.
2. Each teacher teaches only one course. A course can have several teachers.

Step through the two checks. You will rarely hit this case. It needs a table where two different column pairs could each be the key and they share a column: here (student, course) and (student, teacher) both pick out one row. Most tables that pass 3NF also pass BCNF, so many designers simply aim for BCNF. The fourth and fifth normal forms deal with tables that combine several independent lists, such as a person’s skills and languages in one table. You will rarely meet them if each table describes one subject.

​

Breaking the rules on purpose

A normalized design stores each fact once, but answering a question can take several joins. Sometimes a design copies a fact on purpose to make reads faster or simpler. This is called denormalization. For example, the shop could store totalPrice in Orders so the order list doesn’t add up OrderDetails every time. Try it on order 1001: 2 pens at 1.50 and 1 notebook at 4.00. The stored total is a second copy of a fact, and every change to an order line must update it too. So:

• Denormalize only after you measure a real slowdown, not “just in case”.
• Write down which column is a copy and what keeps it in sync, such as a trigger, the application, or a nightly job.
• Consider a view or a materialized view first. It gives the fast read without a hand-maintained copy.

​

Rules the database enforces for you

Good tables are half the job. The other half is writing the rules into the table definition, so the database rejects bad data on every write. You write each rule once; no application can skip it. Try some writes against OrderDetails. The rules fall into three groups. The Relational Model covers them in more detail.

• Entity integrity: no part of a primary key may be NULL.
• Referential integrity: every foreign key points at a row that exists. When a parent row is deleted, you choose what happens to the rows pointing at it: refuse (RESTRICT), delete them too (CASCADE), or empty their foreign key (SET NULL). SET NULL only works on a foreign key that may be empty, so it can’t be used on OrderDetails.orderID, which is part of the key.
• Business rules: facts specific to your shop, such as “quantity is more than 0”. Use CHECK, NOT NULL and UNIQUE, plus column types that fit the data: whole numbers, decimals for money, dates for dates.

​

Indexes

Last, add indexes for the columns you search and join on. An index is a sorted copy of one column that points back at the rows, like the index at the back of a book. It speeds up reads and slows down writes. Primary keys are indexed automatically in most databases; foreign keys often are not. When a row changes, the database updates that row’s index entry; it does not rebuild the whole index. Still, every index adds work to every write, so index for the queries you actually run.

​

Summary

Database design comes down to four steps. Here they are on one screen, with the rule for each and what it meant for the shop. What matters most:

1. Store each fact once. A fact stored twice will one day disagree with itself. That is what update, insert and delete anomalies are.
2. Keys never change. Use a meaningless ID, so no real-world event can break what points at it.
3. Let the database enforce the rules. Primary keys, foreign keys, NOT NULL and CHECK stop bad data on every write, from every app.
4. Copy data only on purpose. Denormalize after you measure a slow read, and write down what keeps the copy in sync.
5. Index what you search. Index the columns you filter and join on, not every column. Each index costs every write.

​

Check yourself

​

Glossary

​

For AI agents

---

Adapted from Chua Hock Chuan, “Relational Database Design”, with corrections on index maintenance and auto-increment, a clearer 3NF example, and a note on storing the price at order time. The shop data is illustrative; figures are simplified teaching models.