Database Design & Leadership: schemas, governance and teaching

01

Before you start, and modelling the data: entities, keys and relationships

In simple words

Before builders pour concrete, an architect draws the house: which rooms there are, which doors connect them, and how many people each room must hold. A data model is that drawing for a database. Mistakes are cheap on paper and expensive after the concrete sets.

Before this course: Advanced SQL: window functions and performance (course 7.4.3). You should already know joins, GROUP BY and HAVING, subqueries, views, CTEs, window functions, indexes and query plans, and you should have the four-table shop from courses 7.4.2 and 7.4.3. Courses 7.4 (SQL Basics) and 7.4.2 (SQL Joins & Analysis) come before that. This is the expert level: until now you wrote queries on tables someone else designed. Now you design the tables, set the rules for your team, review other people's work and teach.

Key facts

An entity is a thing you keep data about (a customer, a workshop, a booking). Each entity usually becomes a table. An attribute is a fact about it (a name, a start time) and becomes a column.
A relationship links entities. Its cardinality says how many on each side:

Cardinality	Example	How to build it
One-to-many	One customer has many orders	Put the "one" side's key in the "many" table (`orders.customer_id`)
Many-to-many	Many customers book many workshops	A middle table with both keys (`bookings`)
One-to-one	One customer has one loyalty card	A key that is also UNIQUE, or the same table

Designers often draw three levels: a conceptual model (entities and relationships, for business people), a logical model (all attributes and keys) and a physical model (real tables, types, indexes for one database).
A primary key identifies each row. A natural key comes from the real world (an email, a product code). A surrogate key is a number the database makes up (id INTEGER PRIMARY KEY). Surrogate keys never need to change when real-world facts change; keep natural keys as UNIQUE columns when they must not repeat.
A foreign key says "this value must exist in that other table". It stops orphans, such as a booking for a customer who does not exist.
SQLite turns foreign key checks off by default for backwards compatibility. You must run PRAGMA foreign_keys = ON; on every new connection. It has no effect inside an open transaction, so run it straight after connecting.
The SQLite documentation says child key columns do not need an index, but one is "almost always beneficial", because without it each check scans the whole child table.
Ask the business questions first: "Can a customer book the same workshop twice?" "What happens to bookings if a workshop is cancelled?" Each answer becomes a key, a constraint or a rule.

Example (a new need: the shop now runs workshops that customers book)

CREATE TABLE customers (
 id  INTEGER PRIMARY KEY,
 name TEXT NOT NULL,
 city TEXT
);
CREATE TABLE workshops (
 id     INTEGER PRIMARY KEY,
 title   TEXT NOT NULL,
 starts_at TEXT NOT NULL,
 seats   INTEGER NOT NULL CHECK (seats > 0)
);
CREATE TABLE bookings (
 id      INTEGER PRIMARY KEY,
 customer_id INTEGER NOT NULL REFERENCES customers(id),
 workshop_id INTEGER NOT NULL REFERENCES workshops(id),
 booked_at  TEXT NOT NULL,
 status    TEXT NOT NULL DEFAULT 'booked'
        CHECK (status IN ('booked', 'cancelled', 'attended')),
 UNIQUE (customer_id, workshop_id)
);
CREATE INDEX idx_bookings_workshop ON bookings(workshop_id);
INSERT INTO customers VALUES (1,'Ana','Toronto'), (2,'Ben','Hamilton'), (3,'Chloe','Ottawa');
INSERT INTO workshops VALUES
 (1, 'Bullet journaling', '2026-12-05 10:00', 8),
 (2, 'Watercolour basics', '2026-12-12 14:00', 6);
INSERT INTO bookings (customer_id, workshop_id, booked_at) VALUES
 (1, 1, '2026-11-20 09:15'), (2, 1, '2026-11-21 18:40'), (1, 2, '2026-11-22 12:05');

The business answers are now built in: UNIQUE (customer_id, workshop_id) means no double booking; the CHECK allows only three statuses; seats > 0 blocks an empty workshop.

Example (the rules at work)

Test	Foreign keys off (the default)	After `PRAGMA foreign_keys = ON;`
Booking for customer 99, who does not exist	Accepted in silence (an orphan row)	`FOREIGN KEY constraint failed`
Ana books workshop 1 a second time	`UNIQUE constraint failed`	`UNIQUE constraint failed`
Status 'maybe'	`CHECK constraint failed`	`CHECK constraint failed`

PRAGMA foreign_key_check; lists any orphan rows already in the database, so you can find them after turning the checks on.

Seats left per workshop (a LEFT JOIN, so a workshop with no bookings still shows):

SELECT w.title, w.seats,
    COUNT(b.id) AS booked,
    w.seats - COUNT(b.id) AS seats_left
FROM workshops w
LEFT JOIN bookings b ON b.workshop_id = w.id AND b.status = 'booked'
GROUP BY w.id, w.title, w.seats
ORDER BY w.id;

Result: Bullet journaling 8 seats, 2 booked, 6 left; Watercolour basics 6 seats, 1 booked, 5 left.

Step by step

Write the business need in one sentence: "Customers book seats in workshops."
Underline the nouns (customer, workshop, booking): these are your entities.
For each pair, ask "one or many on each side?" and write the cardinality.
Write three business questions about edge cases (double booking, cancellation, a full workshop) and get answers before you write any SQL.
Open a new file in sqlite3, run PRAGMA foreign_keys = ON;, then run the CREATE and INSERT statements.
Try the three tests in the table above and read each error message.
Run the seats-left query. Check: 6 and 5.
Check: every rule from step 4 is now a key, a constraint or a written note.

Watch on YouTube

Entity relationship diagram (ERD) tutorial, part 1Lucid Software · YouTube · youtube.com

ERD tutorial, part 2: primary keys, foreign keys and bridge tablesLucid Software · YouTube · youtube.com

Videos by other creators on YouTube, for reference. They are not made by or affiliated with The Future Market.

LESSON 01Before you start, and modelling the data: entities, keys and relationships

LESSON 01 / ANIMATED LESSON / AI VOICE00:00 / 01:15

Voice

Read the video script

[Host] "Until now you queried tables someone else designed. Now you are the architect."
[Screen: three boxes, customers, workshops, bookings, with lines between them] "Nouns become entities. Lines become relationships. Many-to-many needs a table in the middle."
[Text on screen: Can a customer book twice? What if a workshop is cancelled?] "Ask the business questions first. Every answer becomes a key or a constraint."
[Screen: FOREIGN KEY constraint failed] "And in SQLite, switch foreign keys on for every connection, or orphans slip in silently."

Try it now

Extend the workshop model: each workshop has one teacher, and a teacher can run many workshops. Add a teachers table and the right foreign key. Then write two business questions you would ask before deciding what happens to workshops when a teacher leaves.

ClaudeStuck? Copy a ready-made prompt for Claude

Copy this, open Claude, paste it, and Claude will walk you through the lesson step by step.

For a grown-up: Claude is for adults (18+). Copy this, paste it into Claude, and use the answer to explain the lesson to your child.

I'm learning "Database Design & Leadership: schemas, governance and teaching" on The Future Market. Lesson 1: "Before you start, and modelling the data: entities, keys and relationships".

What the lesson teaches:
- An entity is a thing you keep data about (a customer, a workshop, a booking). Each entity usually becomes a table. An attribute is a fact about it (a name, a start time) and becomes a column.
- A relationship links entities. Its cardinality says how many on each side:
- Cardinality - Example - How to build it
- One-to-many - One customer has many orders - Put the "one" side's key in the "many" table (orders.customer_id)
- Many-to-many - Many customers book many workshops - A middle table with both keys (bookings)
- One-to-one - One customer has one loyalty card - A key that is also UNIQUE, or the same table
- Designers often draw three levels: a conceptual model (entities and relationships, for business people), a logical model (all attributes and keys) and a physical model (real tables, types, indexes for one database).

The exercise I'm working on:
Extend the workshop model: each workshop has one teacher, and a teacher can run many workshops. Add a teachers table and the right foreign key. Then write two business questions you would ask before deciding what happens to workshops when a teacher leaves.

I'm stuck. Please:
1. Explain the main idea in plain, simple words with one everyday example.
2. Walk me through the exercise one small step at a time. Ask me what I've done so far before giving the next step.
3. Don't just give me the final answer. Help me do it myself, and check my work when I share it.

I'm a parent helping my child (about 8 years old) with a lesson called "Before you start, and modelling the data: entities, keys and relationships" from the course "Database Design & Leadership: schemas, governance and teaching".

Here's what the lesson says:
Before builders pour concrete, an architect draws the house: which rooms there are, which doors connect them, and how many people each room must hold. A data model is that drawing for a database. Mistakes are cheap on paper and expensive after the concrete sets.

The activity:
Extend the workshop model: each workshop has one teacher, and a teacher can run many workshops. Add a teachers table and the right foreign key. Then write two business questions you would ask before deciding what happens to workshops when a teacher leaves.

Please explain this so an 8-year-old understands: short sentences, a fun everyday example, and no hard words.
Then give me 3 simple questions I can ask my child to check they understood, and a way to do the activity together, one tiny step at a time.

Open Claude

Sources

SQLite, foreign key support (off by default, per connection, actions, child key indexes): www.sqlite.org/foreignkeys.html
SQLite, quirks, caveats and gotchas (foreign keys off by default): www.sqlite.org/quirks.html
SQLite, PRAGMA statements (foreign_key_check): www.sqlite.org/pragma.html

My notes

I learned this

02