Relational model

The index is open. QUERY unfolds a hologram above the desk: thousands of records hang in the air in a neat grid, and thin amber threads run between some of them. You reach for one — it rings like a string. This isn’t a pile of files. It’s a fabric.

Tables, rows, columns

A relational database is like a tidy catalog: the data is sorted into tables, and inside each table there are columns (what properties we store) and rows (the individual records).

Take the buyers’ table users: each row is one buyer from old Earth, and the columns describe their id, full_name, email, country, city, signup_date.

So that people with the same name don’t get confused, every row has a primary key — a unique identifier, usually the id column. Through it, other tables can reference this row using a foreign key. For example, an order in orders has a user_id — that’s how an order remembers its buyer. Those amber threads on the hologram are exactly these key-based links.

QUERY: As long as the key holds, the thread holds. A century and a half on, every order still remembers its person.

Run the query and see what Kotomarket’s buyers look like — records no one has opened since the days of the old Net:

Natural or surrogate key

Where does a primary key come from? There are two paths. A natural key is a real attribute that’s already unique: a buyer’s email, a book’s ISBN, a currency code. A surrogate key is an artificial identifier with no meaning in the outside world: an auto-number or a UUID. Kotomarket uses surrogate id values everywhere — and that’s a textbook choice:

-- Surrogate key: the database hands out the next number itself (PostgreSQL)
CREATE TABLE users (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email     TEXT NOT NULL UNIQUE,  -- the natural candidate lives alongside it as UNIQUE
  full_name TEXT NOT NULL
);

Why this way? The cardinal property of a primary key: it must not change. Foreign keys in other tables point at the PK, and changing its value would drag along an update of every thread at once — in orders, in events, everywhere. And "eternal" natural attributes barely exist: people change their email and surname, phone numbers get reassigned, even ISBNs get reissued. A surrogate id never changes — precisely because it means nothing.

Requirements for any primary key: unique, not NULL, stable. Save natural keys for small, immutable reference tables — the currency code 'RUB' from ISO 4217, say. And the uniqueness of a natural candidate like email you pin down with a separate UNIQUE constraint, as in the example above: the links hold, the data stays honest, and the buyer is still free to change their email.