Relational database concepts
Review a schema
The idea in 30 seconds
Most schema mistakes come from storing a fact in the wrong shape: a list squeezed into one column, or the same fact copied into several tables. Both look convenient on day one. Both mean the database can no longer check your data, and every query has to work around the shape.
Helps to know first: What data is, and why we need a database
On this page
The situation
A small team is starting an online shop and asks an AI assistant for a schema. Customers place orders; each order contains one or more products. This is what came back.
Review taskOrders schema for a new shop
Pull request: “Simple orders schema. One table per order keeps it fast: no joins needed to show an order.”
CREATE TABLE customers (
id integer PRIMARY KEY,
name text,
email text
);
CREATE TABLE orders (
id integer PRIMARY KEY,
customer_email text, -- copied so we can show it without a join
customer_name text, -- same
product_ids text, -- e.g. '1,2,5'
total numeric
);Would you approve it? Write the review you would leave.
Your call
Model review
Request changes. It will be quick to build and painful to keep correct.
1. product_ids hides data the database can no longer check or find. A comma-separated list is just text to PostgreSQL. We loaded three sample orders into this design:
- An order with
product_ids = '4,99'was accepted, though product 99 does not exist. No foreign key can check inside a string. - “Which orders contain product 2?” became
WHERE product_ids LIKE '%2%', which also returned the order containing product 12. - There is nowhere to put a quantity or the price paid per product.
Use a separate order_items table with one row per product in the order, foreign keys to orders and products, a quantity and the unit price, and the primary key (order_id, product_id).
2. Copying the customer’s email and name into every order means they will drift. When a customer changes their email, old orders keep the old one, and nothing tells you which is current. Store customer_id with a foreign key to customers, and join when you need the name. A join on a primary key is the cheap, indexed lookup you met in B-tree indexes.
3. Nothing is required or unique. Every column can be NULL: an order with no customer, a customer with no email. Two customers can share an email. Add NOT NULL where a value must exist, and UNIQUE on customers.email.
4. total is a third copy of information. It can disagree with the items. Either compute it from order_items when needed, or, if it is stored for speed, decide which one wins and keep them in sync deliberately.
What I would ask for: the four tables in the practice shop database: customers, products, orders with a customer_id foreign key, and order_items keyed by (order_id, product_id).
Compare it with yours. Did your review catch the main problems?
How to review a schema
Ask these questions of every table, in this order:
- What is one row? “One customer”, “one product in one order”. If you cannot say it in a few words, the table is doing two jobs.
- What identifies a row? That is the primary key. If the honest answer is “nothing”, rows can be duplicated and never told apart.
- Is any column holding a list? Comma-separated ids, JSON arrays of ids,
phone1,phone2,phone3. A list usually wants its own table. - Is any fact stored twice? Copies drift. Prefer a key that points to the one true copy, unless you have a measured reason to copy, and a plan to keep the copy in sync.
- What must always be true? Required values, uniqueness, references to other tables, allowed ranges. Write each as a constraint, so the database enforces it instead of every program remembering to.
Check yourself
Why can’t a foreign key protect a
product_idscolumn holding'1,2,5'?Show answer
To the database the column is one text value, not three references. A foreign key compares whole column values against another table’s key, so it has nothing to check inside a string.
What goes wrong when an order stores a copy of the customer’s email?
Show answer
The copy does not change when the customer updates their email. Old orders keep the old address and nothing marks which value is current.
What should you ask first about any table, and why?
Show answer
“What is one row?” If one row cannot be described simply, the table mixes several kinds of thing, which leads to lists in columns, duplicated facts and missing keys.
Finished reading?
Saved in this browser only.
Sources
Where each claim on this page comes from.
- 1.PostgreSQL documentation: 5.5 ConstraintsPrimary· §5.5.5 Foreign Keys
A foreign key requires every value to match a row in the referenced table.
- 2.PostgreSQL documentation: 5.5 ConstraintsPrimary· §5.5.4 Primary Keys
A primary key can span several columns.
Go further
What to read next, and why it is worth the time.
- 1.PostgreSQL documentation: 5.5 ConstraintsPrimary· §5.5 Constraints
Every kind of rule the database can enforce for you. Knowing the list is half of reviewing a schema.
Applies to PostgreSQL 18. Draft: not yet fact-checked end to end.