Almost every app you use keeps its data in a database, and almost every database stores that data in tables. A table looks a lot like a spreadsheet, but it comes with rules a spreadsheet never enforces. This page explains rows, columns, data types and primary keys, and then shows the big idea: why you split data into two tables linked by an id instead of keeping one giant sheet.
Imagine a small online shop. It needs to remember its customers. In a database, that list would be a table called customers:
Anatomy of a table · tap a part
The vocabulary is small:
email. Every column has a name, and every row has a slot for it.[email protected].So far, that's a spreadsheet. The difference is that a database table is declared up front: you tell the database exactly which columns exist and what may go in each one, and from then on it refuses anything that breaks those rules. The language used to talk to most databases is SQL (say "S-Q-L" or "sequel"). Here is how you'd create the table above:
CREATE TABLE customers (
id integer PRIMARY KEY,
name text NOT NULL,
email text NOT NULL,
joined date
);Read it line by line: make a table called customers with four columns. Each line gives a column's name, then its data type, then any extra rules.
A data type says what kind of value a column holds. In a spreadsheet you can type "next tuesday" into a date column and nothing stops you. A database checks every value against the column's type:
integer: a whole number, like 3 or -40. Not 4.5, not "three".text: any string of characters, like a name or an email address.date: a real calendar date, written 2025-01-20 (year-month-day). February 30th is rejected because it doesn't exist.numeric(6,2): an exact decimal number with up to 6 digits, 2 of them after the point. Good for prices: 19.99. (Exact matters: you don't want money rounding errors.)Types are not just tidiness. Because the database knows joined holds dates, it can sort customers by sign-up date correctly, or find everyone who joined in March. If the column were plain text, "10 March" would sort before "9 March", because text sorts character by character.
The extra rule NOT NULL means "this column must always have a value". NULL is the database's word for "no value here at all, unknown". The joined column has no NOT NULL, so a customer whose sign-up date we lost can still be stored, with NULL in that slot. But a customer with no email is refused.
The first column, id, has a special job. It's the table's primary key: a value that identifies exactly one row, forever. The database guarantees two things about it: it's never empty, and no two rows ever share it. Try to add a second customer with id 1 and you get:
ERROR: duplicate key value violates unique constraint "customers_pkey"
DETAIL: Key (id)=(1) already exists.(That's the real message from PostgreSQL, a popular free database. customers_pkey is the name it gave the primary-key rule automatically.)
Why not just use the name? Because names aren't unique: the shop will eventually have two customers called Ada. And why not the email? Emails are unique, but they change. A good primary key is a plain number that means nothing except "this row", so it never needs to change. Most databases can even hand out the next number for you automatically.
Now the shop wants to record orders. The obvious, spreadsheet-style idea is one big table: each order on a row, with the customer's name and email typed next to it so you know who to send it to.
Ada has bought three things, so her email appears three times. Ada now tells you she has a new email address. Click into one of her email cells below and change it.
The spreadsheet mess
In the one-big-table version the same fact, "Ada's email", is stored in several places. Change one copy and forget the others, and the table now contradicts itself. Nobody can tell which address is the real one, and the shipping confirmation might go to the old one. Database people call this an update anomaly, and repeating the same fact in many rows is called redundancy.
The "Delete order 104" button shows a second problem. Alan only ever bought one thing. Delete that order in the big table and Alan's name and email vanish with it, even though he's still a customer. And the reverse: you can't store a new customer at all until they've ordered something, because every row is an order.
The fix is to give each kind of thing its own table: one row per customer in customers, one row per order in orders. Each fact is written down exactly once. Flip the toggle above to "Two linked tables", change Ada's email there, and tap a blue customer_id to see where it points.
In the two-table version, an order doesn't copy the customer's name and email. It stores just one number: customer_id, which is the id of a row in customers. Order 103 says "customer 1", and to find the email you look up customer 1. Because there's only one row for customer 1, there's only one email to keep correct.
A column that holds another table's primary key is called a foreign key ("foreign" because the key belongs to a different table). You declare it with REFERENCES:
CREATE TABLE orders (
id integer PRIMARY KEY,
customer_id integer NOT NULL REFERENCES customers(id),
item text NOT NULL,
price numeric(6,2)
);That one word makes the database a guard for the link. It will refuse an order for a customer who doesn't exist:
INSERT INTO orders VALUES (106, 99, 'Chair', 80.00);
ERROR: insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL: Key (customer_id)=(99) is not present in table "customers".And it will refuse to delete a customer who still has orders, so no order is ever left pointing at nobody. That guarantee is called referential integrity: every link leads somewhere real.
When you want the combined view back (each order with its customer's name and email), you ask for it with a JOIN, which glues rows from two tables together wherever the ids match:
SELECT orders.id, customers.name, customers.email, orders.item
FROM orders
JOIN customers ON customers.id = orders.customer_id
ORDER BY orders.id; id | name | email | item
-----+-------+-------------------+-----------
101 | Ada | [email protected] | Keyboard
102 | Grace | [email protected] | Mouse
103 | Ada | [email protected] | Monitor
104 | Alan | [email protected] | Desk lamp
105 | Ada | [email protected] | USB cable
(5 rows)Looks just like the big spreadsheet! The difference is that this is computed each time you ask. Update Ada's email once in customers, run the query again, and all three of her rows show the new address. You get the convenient view without storing the repetition.
Below are the same two tables. Fill in a new row and press INSERT. The simulator checks it the way PostgreSQL would and shows the actual error message PostgreSQL 16 prints (trimmed to the ERROR and DETAIL lines). Leave a box empty to send NULL. The dashed buttons fill in rows worth trying.
Insert simulator
Not every database is this strict. SQLite, the small database built into phones and browsers, happily stores the text 'hello' in an integer column unless you create the table with the STRICT option, and it only enforces foreign keys after you run PRAGMA foreign_keys = ON;. PostgreSQL and MySQL check types and foreign keys out of the box.
A good rule of thumb for beginners: one table per kind of thing, one row per thing. Ask "what are the nouns?". A shop has customers, orders and products; a school has students, courses and teachers. Then ask, for each piece of information, "whose fact is this?". An email belongs to a customer, so it lives in customers. A price paid belongs to an order, so it lives in orders. If you notice yourself copying the same value into many rows, that's the sign it belongs in its own table, referenced by id.
This process of splitting data so each fact is stored once has a formal name, normalization, and a whole theory behind it. You don't need the theory yet. "Don't write the same fact twice" gets you most of the way.
Which column is the best primary key for a customers table?
Names repeat (two customers called Ada) and emails change. A plain number that means nothing but "this row" stays unique and never needs to change.
With separate customers and orders tables, Ada has 50 orders and changes her email. How many values must you update?
Her email is stored once, in her customers row. The 50 orders only hold her customer_id, which doesn't change.
orders.customer_id is a foreign key to customers(id). You insert an order with customer_id 7, but there is no customer 7. What happens?
Guarding links is the foreign key's whole job: PostgreSQL rejects it with "violates foreign key constraint". (SQLite only does this once foreign keys are switched on.)
A date column receives the value '2023-02-29'. What does PostgreSQL do?
2023 isn't a leap year, so February 29th doesn't exist: date/time field value out of range. '2024-02-29' would be fine.
integer, text, date…) and can have rules like NOT NULL. The database rejects values that break them.JOIN to see them together.Next time an app lets you change your email in one place and it's instantly right everywhere, you'll know why: somewhere, a single row in a users table just changed.