Database design basics: tables, keys and relationships, with a real example

A well-designed database is boring in the best way: data goes in once, comes out correct, and doesn’t contradict itself. A badly designed one leaks problems into every part of the app, from wrong totals on invoices to customers who exist twice. This guide walks through the basics with one example, a small online shop, so you can see each decision in context.

The SQL below is tested on SQLite. It works almost unchanged on PostgreSQL and MySQL.

Start with the things, not the screens#

Before writing any SQL, list the things your business keeps track of. For a shop:

• Customers buy things.
• Products are what you sell.
• Orders are a customer buying something on a date.
• Order items are the lines on an order: which product, how many, at what price.

Each of these becomes a table. Each row in a table is one of those things, and each column is one fact about it. A good test: if you’d write it on a separate index card, it’s probably its own table.

A common mistake is designing tables around pages instead, such as one big checkout_form table with every field from the checkout page. It works until the same customer orders twice, and now their email lives in two places.

Give every row a primary key#

A primary key is a column that identifies one row and never changes. Use a plain number or a UUID that has no meaning of its own:

CREATE TABLE customers (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  email      TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Why not use the email as the key? Because people change their email. If the email were the key, every order pointing at that customer would need updating too. A meaningless id never needs to change, so nothing that points at it breaks.

UNIQUE on email still stops two customers having the same address, and NOT NULL means the column can’t be left empty.

Connect tables with foreign keys#

An order belongs to one customer, and a customer can have many orders. That’s a one-to-many relationship, and you store it on the “many” side:

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  status      TEXT NOT NULL DEFAULT 'pending',
  created_at  TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

customer_id is a foreign key: it points at a row in customers. With REFERENCES, the database refuses an order for a customer who doesn’t exist. In SQLite you have to switch this check on with PRAGMA foreign_keys = ON; for each connection; PostgreSQL and MySQL (InnoDB) enforce it by default.

Many-to-many needs a table in the middle#

One order can contain many products, and one product appears in many orders. That’s many-to-many, and you can’t store it with a single column on either side. Instead, add a table where each row is one link:

CREATE TABLE products (
  id          INTEGER PRIMARY KEY,
  name        TEXT NOT NULL,
  price_cents INTEGER NOT NULL CHECK (price_cents >= 0)
);

CREATE TABLE order_items (
  order_id         INTEGER NOT NULL REFERENCES orders(id),
  product_id       INTEGER NOT NULL REFERENCES products(id),
  quantity         INTEGER NOT NULL CHECK (quantity > 0),
  unit_price_cents INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Three details worth copying:

• The primary key is two columns. (order_id, product_id) means the same product can’t appear twice on one order; you increase quantity instead.
• The price is copied onto the order line. Prices change. An order from March must keep showing the March price, so unit_price_cents records what the customer actually paid.
• Money is stored in whole cents. Decimal fractions like 0.1 can’t be stored exactly as floating-point numbers, so totals drift by tiny amounts. Whole numbers of cents (or a DECIMAL type in PostgreSQL and MySQL) add up exactly.

Store each fact once#

This is the idea behind normalisation, which sounds more academic than it is. The rule of thumb: every fact lives in exactly one place, and everything else points at it.

If the customer’s email were copied onto every order, changing it would mean updating every order, and missing one would leave the data contradicting itself. With the design above, the email lives only in customers, and orders reach it through customer_id.

You get the combined view back with a join:

SELECT o.id, c.name, SUM(i.quantity * i.unit_price_cents) AS total_cents
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id, c.name;

For an order with two mugs at 1200 and one notebook at 800, this returns total_cents = 3200.

The one deliberate exception is the copied price on order_items. It looks like a duplicate, but it’s a different fact: “the price on the day of the order”, not “today’s price”.

Let the database enforce the rules#

Constraints are rules the database checks on every write, whatever code is doing the writing. In the tables above, each of these is rejected with an error:

Check the input in your app too, so people get friendly messages. But the database is the last line of defence: it still holds when a bug, a script or a second app writes to the same tables.

A short checklist#

• One table per thing, one row per instance, one column per fact.
• A meaningless id primary key on every table.
• Foreign keys for relationships, and a middle table for many-to-many.
• Each fact stored once; copy a value only when it means “the value at that time”.
• NOT NULL, UNIQUE and CHECK for the rules that must always hold.
• created_at on tables where you’ll ever ask “when did this happen?”.
• Names that a new developer understands: customer_id, not cid.

Once the tables are right, the next step is making them fast as they grow: see database indexes explained. If you work in Wix, the same thinking applies to CMS collections and reference fields; Wix dynamic pages explained shows them in use.

If you need a data model designed for your site or app, that’s part of my Wix Velo development work.