Databases: tables, keys and why a business needs them

Picture a small online store selling coffee and tea. It has to remember clients, products, orders and what is inside each order. A relational database splits this into tables. The PostgreSQL documentation defines a table as a named collection of rows: every row of a table has the same set of named columns, and each column has its own data type.

A row is one record: one client or one order. A column is one property of all records: name, city, email, sign-up date. The clients table below has eight rows and five columns. The value NULL means "unknown or missing": the client did not give an email or a city. It is neither an empty string nor zero — we will come back to that difference in the SQL lessons.

Tables are grouped into a database, and the database is managed by a DBMS — a database management system. PostgreSQL runs as a server: an application (a website, a CRM, a mobile backend) connects to it over the network, sends queries and receives results. One server handles many connections at the same time.

A primary key is a column or a group of columns that identifies a row unambiguously. According to the PostgreSQL documentation, a primary key requires two things at once: values are unique and never NULL. A table can have only one primary key, and the database automatically builds an index on it so that lookups by key are fast.

Why not use a name or a phone number as the key? Names repeat: a store easily ends up with two clients called Aigerim. Phone numbers and emails change, and sometimes they are missing altogether. If orders reference a value that changes, every related record has to be rewritten when it does. That is why the key is usually a separate numeric id column that means nothing to people and never changes.

CREATE TABLE clients (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       text NOT NULL,
    city       text,
    email      text UNIQUE,
    created_on date NOT NULL DEFAULT current_date
);

CREATE TABLE orders (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    client_id  bigint NOT NULL REFERENCES clients (id),
    ordered_on date NOT NULL,
    status     text NOT NULL
               CHECK (status IN ('new', 'paid', 'shipped', 'cancelled')),
    promo_code text
);

• id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY — the key; the database assigns numbers and you cannot type one in by hand
• name text NOT NULL — a name is required; a row without one will not be saved
• email text UNIQUE — two clients cannot share an email, but the email may be left out
• created_on date NOT NULL DEFAULT current_date — if no date is passed, the database uses today

A key can also span several columns. In the order lines table order_items the key is the pair (order_id, product_id): the same product cannot appear twice in one order as separate rows — the quantity goes up instead.

An order does not store the client's name and city — only the client's number in the client_id column. The REFERENCES clients (id) constraint turns that column into a foreign key: its value must match the primary key of some row in the clients table. The documentation calls this referential integrity — no orphaned orders can appear in the database.

Let us check that the database really guards the link. We try to create an order for client 99, who does not exist, and then delete a client who has orders:

INSERT INTO orders (client_id, ordered_on, status)
VALUES (99, '2026-10-10', 'new');

-- ERROR:  insert or update on table "orders" violates foreign key constraint "orders_client_id_fkey"
-- DETAIL:  Key (client_id)=(99) is not present in table "clients".

DELETE FROM clients WHERE id = 1;

-- ERROR:  update or delete on table "clients" violates foreign key constraint "orders_client_id_fkey" on table "orders"
-- DETAIL:  Key (id)=(1) is still referenced from table "orders".

What happens to dependent rows on delete is the schema designer's decision. By default, deletion is blocked while anything references the row. ON DELETE CASCADE deletes the dependent rows together with the main one — it fits when the dependent record cannot exist on its own. Order lines in the training database work this way: delete an order and its lines go too. Products and orders, however, are independent things, so the database will not let you delete a product someone has already bought.

Look at the order_items table. One order contains many products, and one product appears in many orders — a many-to-many relationship. It is always modelled with an intermediate table holding two foreign keys; the PostgreSQL documentation walks through exactly this schema (products, orders, order_items) in its chapter on constraints. The price in order_items is stored separately from the catalogue: catalogue prices may go up, but the total of an old order must not change.

The column type is the first rule the database checks on write: a date column will not accept the string "tomorrow", a numeric column will not accept "twelve". The training database needs only a few types:

A type is often not enough: a price is a number, but it must not be negative. Constraints cover such rules. If a write breaks one, PostgreSQL rejects the whole row and tells you which rule fired:

UPDATE orders SET status = 'delivered' WHERE id = 1;

-- ERROR:  new row for relation "orders" violates check constraint "orders_status_check"
-- DETAIL:  Failing row contains (1, 1, 2026-09-02, delivered, null).

INSERT INTO clients (name, email)
VALUES ('Aigerim S.', 'aigerim@example.com');

-- ERROR:  duplicate key value violates unique constraint "clients_email_key"
-- DETAIL:  Key (email)=(aigerim@example.com) already exists.

One subtlety: a CHECK constraint is satisfied when its condition is true or NULL. So weight_g integer CHECK (weight_g > 0) lets you leave the weight of a mug empty but will not accept a negative weight. When a value is required, add NOT NULL next to CHECK. The documentation advises that in most designs the majority of columns should be NOT NULL.

An Excel or Google Sheets table also has rows and columns, and at the start that is often enough. The difference shows when data and people multiply. The business signs of that moment are covered in the article "When to move off Excel"; here is the technical side.

Transactions deserve a separate note. The PostgreSQL documentation describes them like this: several steps are bundled into one all-or-nothing operation, intermediate states are not visible to other users, and if a failure occurs none of the steps take effect. For a store this means an order cannot end up paid without the stock being written off. Transactions have their own lesson in this learning path.

Everything above applies to relational databases: data lives in tables, links are expressed with keys and questions are asked in SQL. The most common relational DBMSs in web development are PostgreSQL and MySQL. As the MDN glossary notes, most databases in web development work this way, and databases with a different data model are called NoSQL.

It is not a contest: one project often uses both — PostgreSQL stores orders and Redis caches frequent responses. For bookkeeping, where links and accuracy matter, you start with a relational database. When and why to add NoSQL is covered in a separate comparison in the Databases track.

SQL is the query language of relational databases. Its key trait: you describe the result you want, not how to get it. You do not write "open the orders table, loop over the rows, find the client for each" — you name the tables, the link and the condition. How exactly the data is read is up to the DBMS.

SELECT o.id, o.ordered_on, c.name, o.status
FROM orders AS o
JOIN clients AS c ON c.id = o.client_id
WHERE c.city = 'Almaty'
ORDER BY o.ordered_on;

The query reads almost like a sentence: select the number, date, name and status from orders, joining clients on client_id, where the city is Almaty, sorted by date. Each part — SELECT, WHERE, ORDER BY, JOIN — is covered step by step in the next lessons on the same training database. You can start it with one Docker command and repeat every example yourself.