SQL / DATA MODELLING AND NORMALISATION
Sketching entities and relationships before creating tables
Turn a plain-language requirement into a sketch of boxes, attributes and labelled links, then transcribe that sketch into correct CREATE TABLE statements.
What you will learn
- Mine requirement sentences for nouns as candidate boxes and verbs as candidate links.
- Separate entity from attribute by asking whether it holds facts of its own.
- Read each link in both directions and record whether each end is optional.
- Transcribe a finished sketch: box to table, link to foreign key, rule to constraint.
Understanding Sketching entities and relationships before creating tables
A sketch is three kinds of mark: boxes for the things the system must remember facts about, short labels inside them for the facts themselves, and lines for the sentences that tie boxes together. It exists because being wrong is free at this stage and expensive later; moving a line on paper takes a second, while moving a foreign key after a table holds rows means a migration, a backfill, and every query that assumed the old shape. The sketch is also the only artefact you can argue about with a person instead of with the engine, because whoever described the work can read the lines back as sentences and tell you which ones are false.
Start from the description in short plain sentences and mine them: nouns are candidate boxes, verbs are candidate lines. A noun earns a box when you must store more than one fact about it, or when several other boxes need to point at the same instance of it; otherwise it is a column on the box it describes. The complementary test is to finish the sentence "one row here is exactly one ___" for every box, and if you cannot finish it without the word "and", the box is really two boxes.
Every line then gets read twice, once from each end, with a count and a word about optionality: one bicycle has zero or more repair jobs, one repair job is for exactly one bicycle. The second reading is the one people skip, and it is the reading that decides which table carries the foreign key column and whether that column may be NULL. Once both readings and the row-identity sentences are written down, producing DDL is transcription rather than design: a box becomes a table, a fact becomes a column, a mandatory end of a line becomes a NOT NULL foreign key, and a row-identity sentence becomes a PRIMARY KEY or UNIQUE. That mechanical last step is exactly why the disagreements have to happen in the sketch.
-- SQLite. The sketch comes first, in comments, before any DDL.
--
-- CUSTOMER --owns 0..n--> BICYCLE --brought in for 0..n--> REPAIR_JOB
--
-- Read back the other way, which is where the columns come from:
-- one BICYCLE has exactly 1 owner -> customer_id on bicycle, mandatory
-- one REPAIR_JOB is for exactly 1 bicycle -> bicycle_id on repair_job, mandatory
--
-- One row of BICYCLE is exactly one physical frame in the shop.
-- colour stays an attribute: nothing else needs to store facts about a colour.
CREATE TABLE customer (
customer_id INTEGER PRIMARY KEY,
full_name TEXT NOT NULL
);
CREATE TABLE bicycle (
bicycle_id INTEGER PRIMARY KEY,
frame_number TEXT NOT NULL UNIQUE,
colour TEXT,
customer_id INTEGER NOT NULL REFERENCES customer(customer_id)
);
CREATE TABLE repair_job (
job_id INTEGER PRIMARY KEY,
bicycle_id INTEGER NOT NULL REFERENCES bicycle(bicycle_id),
dropped_off TEXT NOT NULL,
description TEXT NOT NULL
);
INSERT INTO customer (customer_id, full_name) VALUES
(1, 'Ada Okafor'),
(2, 'Bruno Salt');
INSERT INTO bicycle (bicycle_id, frame_number, colour, customer_id) VALUES
(10, 'FR-8821', 'green', 1),
(11, 'FR-9004', 'black', 1),
(12, 'FR-7710', 'red', 2);
INSERT INTO repair_job (job_id, bicycle_id, dropped_off, description) VALUES
(100, 10, '2026-03-02', 'rear wheel true'),
(101, 10, '2026-04-18', 'new brake pads'),
(102, 12, '2026-04-19', 'bottom bracket');
SELECT c.full_name, b.frame_number, j.dropped_off, j.description
FROM repair_job j
JOIN bicycle b ON b.bicycle_id = j.bicycle_id
JOIN customer c ON c.customer_id = b.customer_id
ORDER BY j.job_id;A sketch pins down what one row means and how the boxes connect while changing your mind is still free, so that writing the DDL becomes transcription instead of guessing.
Worked examples
Optionality on the sketch becomes nullability in the table
Shows how the words "exactly one" and "at most one" on the two lines leaving a box turn into NOT NULL or a nullable column.
-- SQLite
-- Sketch lines out of JOB:
-- one JOB is for exactly 1 bicycle (mandatory)
-- one JOB is handled by 0..1 mechanic (optional until someone picks it up)
CREATE TABLE mechanic (
mechanic_id INTEGER PRIMARY KEY,
nickname TEXT NOT NULL
);
CREATE TABLE job (
job_id INTEGER PRIMARY KEY,
bicycle_id INTEGER NOT NULL,
mechanic_id INTEGER REFERENCES mechanic(mechanic_id),
description TEXT NOT NULL
);
INSERT INTO mechanic VALUES (1, 'Kit');
INSERT INTO job (job_id, bicycle_id, mechanic_id, description)
VALUES (100, 10, NULL, 'gear cable frayed');
SELECT job_id, mechanic_id IS NULL AS waiting FROM job;
INSERT INTO job (job_id, bicycle_id, mechanic_id, description)
VALUES (101, NULL, 1, 'puncture');Example explained
Line 1bicycle_id is NOT NULL because that end of the line was marked mandatory: a repair job that is for no bicycle is not a repair job.
Line 2mechanic_id deliberately omits NOT NULL, which is how "0..1 mechanic" becomes storable — the row can exist before anyone is assigned.
Line 3The first insert therefore succeeds with NULL, and the query reports waiting = 1 for that job.
Line 4The last insert is refused by the engine rather than by application code, because the mandatory end of the line was written into the table definition.
Writing down what one row means
Turns the sketch sentence "one row is one bay, on one date, in one slot" into a constraint the engine can check.
-- SQLite
-- Sketch note on the BAY_BOOKING box:
-- one row = one bay, on one date, in one slot
CREATE TABLE bay_booking (
booking_id INTEGER PRIMARY KEY,
bay_number INTEGER NOT NULL,
book_date TEXT NOT NULL,
slot TEXT NOT NULL,
job_id INTEGER NOT NULL,
UNIQUE (bay_number, book_date, slot)
);
INSERT INTO bay_booking (booking_id, bay_number, book_date, slot, job_id)
VALUES (1, 2, '2026-05-04', 'morning', 100),
(2, 2, '2026-05-04', 'afternoon', 101);
SELECT bay_number, book_date, slot, job_id FROM bay_booking ORDER BY slot;
INSERT INTO bay_booking (booking_id, bay_number, book_date, slot, job_id)
VALUES (3, 2, '2026-05-04', 'morning', 102);Example explained
Line 1The UNIQUE list is the sketch sentence itself: the three columns that together identify one real-world booking.
Line 2booking_id only numbers the rows, so on its own it would happily allow the same bay, date and slot twice.
Line 3ORDER BY slot sorts text, which is why 'afternoon' prints before 'morning'.
Line 4The third insert repeats bay 2 on that date in that slot and is rejected, so the sentence written in the sketch is now enforced.
Important notes
A sketch needs no diagram tool. Boxes, lines and the two directional sentences kept in a comment block above the DDL are enough, and leaving them there records why the columns are shaped as they are.
In SQLite a REFERENCES clause records the line but is not enforced until PRAGMA foreign_keys = ON; the NOT NULL and UNIQUE constraints used above are enforced regardless.
Common mistakes
Sketching the spreadsheet you were handed instead of the things it describes, so its column headings become attributes and jan_hours, feb_hours sit in a box; every new month then needs an ALTER TABLE.
Promoting every noun to a box, so colour, status and size become tables holding nothing but a code and its own label, and each ordinary query grows a join that answers nothing.
Reading a link in one direction only — "a customer has bicycles" — so nobody asks whether a bicycle can have two owners; the answer turns up after the table holds data and the foreign key has to move.
Try it yourself
Change, predict, then run
Sketch a tool-lending shelf as a comment block: the boxes, their attributes, and each line read from both ends with a count and optionality. Then transcribe it into CREATE TABLE statements and prove one mandatory end by attempting an insert that leaves it empty.
Open the SQL workspaceCheck your understanding
A sketch line reads: a van is serviced by at most one garage, a garage services many vans, and a van with no garage yet must still be recorded. What does that single line dictate about the tables?
- A nullable garage_id column on van
- A NOT NULL garage_id column on van
- A van_id column on garage
- A van_garage junction table with one row per servicing relationship
Show answer
The link is many-to-one from van to garage, so the pointing column belongs on the many side, van; and because a van with no garage must still be storable, that column has to accept NULL, ruling out the NOT NULL version. A junction table feels safer but encodes "a van may have several garages", which contradicts "at most one" as written, and a van_id on garage could only ever record one van per garage, which is backwards.