Modeling, ORM & Architecture · 01 Entities, Keys & Data Types

From a paragraph of requirements to a set of tables

The client gives you prose. You need entities. Walk it with Next — six steps from one sentence to three tables.

1Nouns are candidates, not answers. Every noun in the brief goes on the list first — filtering comes after.

2An entity has an independent life. If you would ever look it up on its own, it is a table. If it only describes something else, it is a column.

3"Slot" failed that test. Nobody asks for slot #48 — they ask for the appointment at 09:30. It is the appointment's starts_at, not a table.

4Verbs become relationships. "Patients book appointments with doctors" is the entire foreign-key layout, written in English.

Getting this wrong is the expensive mistake. A wrong column type is one migration. A missing entity is a rewrite of every query, struct and handler that touched it — which is why sessions 01 to 04 happen before a single line of Go.

Primary keys identify; foreign keys connect

Two jobs, often confused. A primary key answers "which row is this?" — a foreign key answers "which row over there does it belong to?"

schema.sql — the three tables from the animation

CREATE TABLE patients (
  id            integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name     text        NOT NULL,
  date_of_birth date        NOT NULL,
  phone         text,                        -- text, not integer — see 1c
  created_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE doctors (
  id            integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name     text        NOT NULL,
  licence_no    text        NOT NULL UNIQUE,   -- a natural key, kept as a constraint
  created_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE appointments (
  id            integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  patient_id    integer     NOT NULL REFERENCES patients(id),
  doctor_id     integer     NOT NULL REFERENCES doctors(id),
  starts_at     timestamptz NOT NULL,
  duration_min  integer     NOT NULL DEFAULT 15,
  status        text        NOT NULL DEFAULT 'booked',
  fee           numeric(10,2) NOT NULL
);

PKSurrogate over natural. licence_no identifies a doctor in the real world, but real-world identifiers get reissued, corrected and mistyped. Use a meaningless id as the key and keep the natural one as a UNIQUE constraint — you get both.

FKThe foreign key lives on the "many" side. An appointment has one patient, a patient has many appointments — so patient_id sits on appointments. Session 02 makes that rule general.

+GENERATED ALWAYS AS IDENTITY is the modern standard spelling. serial still works, but identity columns are SQL-standard and cannot be overwritten by accident.

✗A foreign key with no REFERENCES is just a number. Without the constraint the database will happily store patient_id = 9999 for a patient that never existed.

MySQL

MySQL spells the identity column id INT AUTO_INCREMENT PRIMARY KEY and needs ENGINE=InnoDB for foreign keys to be enforced at all — MyISAM parses REFERENCES and silently ignores it.

The type is the first constraint you write

Every column has a tempting type and a correct one. The tempting one is almost always text or float.

Column

😈 Tempting

✓ Correct

starts_at

text — "2026-03-01 09:30"

timestamptz

fee

float / real

numeric(10,2)

phone

integer

text

status

free text

text + CHECK

date_of_birth

timestamptz

date

✗Time as text cannot be compared, sorted or offset. '2026-03-01 09:30' < '2026-03-01 10:00' happens to work; add a timezone and it stops. timestamptz stores an instant and does the arithmetic for you.

✗Money as float is the classic. 0.1 + 0.2 is not 0.3 in binary floating point, and after ten thousand invoices the total is visibly wrong. numeric is exact decimal.

✗Phone as integer loses the leading zero, refuses +, and invites someone to do arithmetic on it. A phone number is an identifier that happens to use digits.

+date vs timestamptz. A birthday has no time and no timezone — storing it as an instant means someone born on the 1st appears to be born on the 31st in another region.

Mistake

Reaching for varchar(50) out of habit. In PostgreSQL varchar(n) and text are the same type with a length check bolted on — the limit buys no performance, and it is the constraint you will most often have to migrate away. Use text, and add a CHECK when a real limit exists.

TASK

Model it before you are told the answer

Take this brief: "A clinic runs rooms. Each appointment happens in one room. Rooms have a floor and a capacity, and some are equipped for X-ray." (a) Decide whether room is an entity or a column on appointments, and write one sentence defending it. (b) Write the CREATE TABLE for whatever you decided, with a primary key and the right type for every column. (c) Name the one column where you were tempted by text and chose something better.

Recap

Session 01 in four lines

1Nouns in the brief are candidate entities; a thing is a table only if you would ever look it up on its own.

2A primary key identifies a row. Prefer a meaningless surrogate id, and keep real-world identifiers as UNIQUE.

3A foreign key connects rows, sits on the "many" side, and is worthless without REFERENCES.

4The column type is your first constraint: timestamptz not text, numeric not float, text not integer for phone numbers.