Database Design Basics: Plan Your Tables, Keys and Links First

by Souy Soeng /October 09, 2026

Diagram of four linked database tables for an online store: customers, orders, order items and products, joined by keys

Database design is the planning you do before storing any data: deciding which tables exist, what each column holds, and how the tables connect. It sounds like paperwork, but it is the reason some apps keep clean data as they grow while others slowly fill up with duplicates and contradictions.

This guide walks through a simple three-step approach: list the real-world things you track, give every row a primary key, and link tables with foreign keys. Along the way, we'll take apart a messy orders table, rebuild it for a small online store, and look at the three shortcuts that cause most beginner problems.

In short

Database design means planning tables, columns and connections before any data goes in.
Each real-world thing you track, such as customers, products or orders, gets its own table.
A primary key gives every row a unique, stable ID that never repeats.
A foreign key stores a pointer to another row instead of copying its details.
Avoid lists in one cell, missing keys and repeated data.

Why does one big table break as your app grows?

Many small apps start with all their data in one big table, like a spreadsheet. At first it feels simple and fast, and that's the trap. Nothing breaks on day one. The problems only appear later, once there are real users and real data, and by then they are harder to untangle.

Picture opening that table one morning. The same customer appears on every order she ever placed, and her email is spelled three different ways. Which version is right? Because each new order re-types her name, email and address, every repeat is another chance for a small typo to slip in and stay there.

Then she moves house. You update her address on one row, but older rows still hold the old one, so your data now disagrees with itself. Even basic questions like 'how many customers do we have?' turn into guesswork, because one person may show up under several slightly different spellings.

Copies everywhere: customer details re-typed on every order
Fixes go missing: updating one copy leaves the others wrong
Questions get hard: counting needs cleanup first

What is database design, and why does it matter?

Database design means planning your data before you store it. Think of it as the blueprint stage: you wouldn't build a house by stacking bricks and hoping for the best. A good design answers three questions up front. What separate things are we storing? What details does each one have? How do they relate to each other?

A table holds one kind of thing, like a labelled drawer in a filing cabinet. A customers table holds customers and nothing else. Columns are the details you record about that thing, such as a name, an email or a price, and each column should hold one simple piece of information. Connections tie the drawers together, so you can say which customer placed which order without copying data.

Good structure prevents three related problems: duplicate data, update errors and confusing queries. They share one fix, which is the rule behind everything else in this guide: store each fact in exactly one place, then point to it from anywhere that needs it.

Duplicate data wastes space and invites typos
Update errors happen when one copy of a fact gets missed
Confusing queries need long, fragile workarounds
The fix: one fact, one place

What's wrong with a messy orders table?

Here is a typical first attempt. It is a very natural thing to build, which is why it's worth examining. Notice that this single table is trying to describe three different things at once: customers, orders and products. That mixing is the root of the trouble.

Ana placed two orders, so her name and email appear twice, and the second email is missing a letter. Her details belong to Ana, not to each order, yet they're copied onto every order row. That copying is exactly how the typo got in.

The items column is trickier. 'Pen, ink' looks readable to a person, but the database sees one piece of text. Asking how many pens you sold becomes a text-searching puzzle. The real problem isn't the typo; it's that three kinds of things are tangled together. The next three steps pull them apart.

# orders: everything in one table
order | name | email        | items
5001  | Ana  | ana@mail.com | pen, ink
5002  | Ana  | ana@mail.co  | paper
5003  | Ben  | ben@mail.com | pen

Step 1: List the things you track and give each its columns

Before thinking about columns, list the real-world things your app keeps track of. A handy trick is to describe your app in plain sentences, such as 'a customer places an order for some products', and pull out the nouns. Keep the ones that are real things with their own details, and drop words that are only details.

A customer exists even when they aren't buying anything, and has a name and an email. A pen has a name and a price whether or not anyone has ordered it. An order is an event: someone bought something at a certain time. For a small shop, that gives three tables instead of one tangled one.

Next, decide which details belong where. A column belongs in a table only if it describes that table's thing. When you're unsure, use the ownership test: whose detail is this? An email belongs to a customer, not an order, so it lives once in the customers table. Also keep each cell to a single value. If something can have many values, you probably need another table.

Customers: name, email, address (changes when they move)
Products: name, price, stock (changes when the price goes up)
Orders: date, status (changes when an order ships)

Step 2: What is a primary key?

Clean tables still leave a question: how do you point to one exact row when two customers could both be called Ana Lopez? A primary key is a column whose only job is to identify each row, with no duplicates allowed. It works like a student ID number: two students can share a name, but never an ID.

The database enforces this for you. If you try to add a second row with the same key, it refuses, and that guarantee is what makes keys trustworthy. Choose something stable. An email feels unique, but people change emails. A plain number, often generated automatically by the database, never needs to change, so nothing pointing to it breaks.

Every table gets one: customers.id, products.id and orders.id. Without keys, there's no reliable way to find, update or connect a specific row.

Unique: no two rows share a key
Stable: use a plain ID rather than a value that might change
Present in every table from day one

Step 3: How do foreign keys link tables together?

Once every row has an ID, you can connect tables. Instead of copying a customer's details into each order, the order stores only the customer's ID. That stored ID is a foreign key. It works like a book's index, which gives you a page number rather than copying the whole page.

In the SQL below, the orders table has its own primary key, id. The customer_id column holds the ID of whoever placed the order, and REFERENCES tells the database that value must match a real customer. Ana's orders simply say 'customer 42', and her email lives in exactly one place. When she changes it, you update one row and every order sees the new value. The database also blocks orders that point to customers who don't exist.

To answer 'who placed order 5001?', the database finds order 5001 by its primary key, reads its customer_id of 42, jumps to row 42 in customers, and returns Ana with her single correct email. In SQL this hop is called a join: you ask for orders and customers together, matched on customer_id. Because keys are unique, there's no guessing between three versions of Ana.

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT
    REFERENCES customers(id),
  order_date DATE
);

Example: designing tables for a small online store

Putting the three steps together for an online store produces four tables. Customers place orders, each order points back to one customer, and a fourth table, order_items, handles the list-in-a-cell problem. Instead of cramming 'pen, ink' into one cell, each order gets one order_items row per product, and each of those rows points to the product it's about.

Ana's order 5001 becomes one row in orders plus two rows in order_items: one for the pen, one for the ink. There's no text to split. The earlier questions become easy: to count pens sold, count the order_items rows for the pen; to count customers, count the customers table.

This pattern sits behind nearly every app that remembers something. A school system links each grade to one student ID and one course ID, much like order_items. A bank links each transaction to an account by its key, so balances stay traceable. The recipe stays the same: find the things, give them keys, link them.

customers → orders (a customer places orders)
orders → order_items (an order contains items)
order_items → products (each item points to a product)

Common database design mistakes beginners make

Most messy databases come from the same three shortcuts. Each one feels like a time-saver in the moment, which is why they're tempting. They also don't fail loudly. They fail quietly, months later, when fixing them is much harder.

The first is storing lists in one column, like 'pen, ink', which stops you from counting, sorting or linking items properly. The second is skipping keys, which leaves no safe way to point at a row and lets duplicates creep in unnoticed. The third is repeating data: if you catch yourself copying a name or a price into another table, stop and link to it instead.

Lists in one column → move them into their own table, one row per item
Skipping keys → give every table a primary key from day one
Repeating data → store each fact once and link with a foreign key

How to plan your tables before you code

The easiest way to avoid these problems is to make planning a habit. Before writing code, spend a few minutes sketching your tables, even on paper. A rough plan is much easier to change than a live database full of real data.

Describe your app in plain sentences and list the real-world things; each becomes a table. Write the columns each table needs, one value per cell, and add a primary key. Draw lines between related tables and turn each line into a foreign key, adding a linking table wherever you see a list. Finally, test the plan with real questions like 'who ordered this?' or 'how many did we sell?' If any answer needs guesswork or copied data, fix the design while it's still a drawing.

List the things
Add columns and primary keys
Draw the links with foreign keys
Test with real questions

Key takeaways

Database design is planning tables, columns and connections before storing data.
Turn each real-world thing you track into its own table.
Give every table a stable primary key from the start.
Link tables with foreign keys instead of copying details.
Use a separate table for lists rather than packing them into one cell.
Test your design with real questions before you build it.

Frequently asked questions

What is the difference between a primary key and a foreign key?

A primary key uniquely identifies each row within its own table, like customers.id. A foreign key is a column in another table that stores that ID to point back to the row, like customer_id in the orders table.

Why shouldn't I use an email address as a primary key?

Email addresses feel unique, but people change them. A plain number, often created automatically by the database, never needs to change, so every foreign key pointing to it keeps working.

How do I store an order with several products?

Don't put a list like 'pen, ink' in one cell. Add an order_items table with one row per product, where each row points to its order and to its product.

Is one big spreadsheet-style table ever good enough?

It can feel fine at first with little data. As the app grows, repeated details, missed updates and hard-to-answer questions start to appear, which is why planning separate linked tables early saves time later.

What is a JOIN in SQL?

A join combines linked rows from two tables in a single query. For example, you can ask for orders and customers together, matched on customer_id, to see who placed each order.

Watch the full video on YouTube →

Tag

Ad