Topic

Project planning & ERD

Definition

An entity-relationship diagram (ERD) records the entities, their key attributes, and the relationships and cardinalities between them, providing an agreed schema plan to write against before a migration or entity file exists.

In simpler words

Before you write any code, sketch which things exist, what they hold, and how they connect — the migration should follow the sketch, not invent it.

Schema mistakes are the most expensive to undo, because data and every query, DTO, and screen already depend on them. Planning the entities and relationships first — as an ERD — makes those decisions explicit and cheap to change while they are still just a sketch.

Open official example (opens in new tab)

After this you can

Explain why a schema is planned before entities or migrations exist
Name an ERD’s building blocks: entities, attributes, keys, relationships, cardinality, optionality
Read a users/tickets ERD and state its cardinalities out loud
Choose an ON DELETE rule (CASCADE / SET NULL / RESTRICT) for a foreign key
State a schema extension in plain language before writing any SQL

Why plan the schema before you write code

Definition

Schema planning is deciding which entities exist, what they store, and how they relate before any entity class or migration is written.

In simpler words

Decide the shape first; the migration should encode a decision you already made, not invent one as you type.

A schema is the hardest thing to change later: once rows exist and queries, DTOs, and UI depend on a column, renaming it or re-pointing a relationship is a data migration, not a quick edit.

An ERD is cheap to change and safe to throw away — a diagram or a few lines of text — so it is the right place to get relationships, keys, and nullability right.

Rule of thumb: the migration follows the sketch. If you are discovering the relationships while writing the migration, you are planning in the most expensive place.

The building blocks of an ERD

Definition

An ERD is built from entities (tables), their attributes (columns), one primary key per entity, foreign keys that point at other entities, and relationship lines annotated with cardinality and optionality.

In simpler words

Boxes are things, lines are how they connect, and the marks on each line say how many and whether the link is optional.

Entity: a thing you store rows of (User, Ticket). Attribute: a column on it (email, status). Primary key: the column that uniquely identifies a row, usually a uuid id.

Foreign key: a column that references another entity’s primary key (assignee_id → users.id) — it is what turns two tables into a relationship.

Cardinality: how many on each side — one-to-one (1:1), one-to-many (1:N), or many-to-many (N:N, which needs a join table).

Optionality (nullability): whether the link is required or optional — a nullable foreign key means the relationship may be absent.

Relationship notation

1 ── 1      one-to-one
1 ── 0..N   one-to-many (optional on the many side)
N ── N      many-to-many (needs a join table)
email*      * marks a unique attribute

Cardinality (1, N) and optionality (0.. means the side can be empty) are the two things every relationship line must state.

Read a worked ERD: users and tickets

Definition

A worked example: a User entity and a Ticket entity connected by an optional many-to-one relationship, where many tickets may reference at most one assignee and a ticket may have none.

In simpler words

Every ticket points at zero or one user through assignee_id; a user can be the assignee of many tickets.

User: id (uuid pk), email (unique), passwordHash, name, role (admin | member).

Ticket: id (uuid pk), title, description (nullable), status (open | in_progress | done), assignee_id (nullable fk → users.id, ON DELETE SET NULL), created_at, updated_at.

The relationship is 1:N and optional — User 1 ── owns 0..N Ticket — and the nullable assignee_id is exactly what makes "a ticket with no assignee" representable.

ERD in text form

User (id, email*, name, role, passwordHash)
  1 ── owns 0..N ── Ticket (id, title, description, status, assignee_id, created_at, updated_at)
                        assignee_id -> User.id (nullable, SET NULL)

The same diagram, written as SQL columns and a foreign key, is exactly what the first migration produces.

Cardinality, nullability & ON DELETE

Definition

For every foreign key, three things must be decided deliberately: its cardinality, whether it is nullable, and its ON DELETE behavior — what happens to this row when the referenced row is deleted.

In simpler words

When the thing you point at gets deleted, ON DELETE decides whether you vanish too, keep pointing at nothing, or block the delete.

ON DELETE SET NULL keeps this row and clears the link — right for assignee_id, since deleting a user should unassign their tickets, not delete them.

ON DELETE CASCADE deletes this row too — right for a child that cannot exist alone, like a comment that has no meaning without its ticket.

ON DELETE RESTRICT (or NO ACTION) blocks the delete while children exist — right when losing the parent would silently orphan important data.

Nullability compounds the choice: a nullable foreign key means every future query and DTO must handle the "no link" case, so make it optional on purpose, not by default.

Choosing ON DELETE per relationship

Ticket.assignee_id  -> User.id      SET NULL   // unassign, keep the ticket
Comment.ticket_id   -> Ticket.id    CASCADE    // comment dies with its ticket
Invoice.customer_id -> Customer.id  RESTRICT   // block deleting a billed customer

The right rule depends on whether the child can exist without the parent — decide it when you draw the line, not when a delete fails in production.

Plan an extension before writing SQL

Definition

Extending a schema starts by stating the new or changed entity, its attributes and constraints, and its relationships to existing entities in plain language, so the migration and DTOs that follow only encode decisions already made.

In simpler words

Write down what it is, what it holds, and what it relates to first — then let the migration generator encode that decision.

A plausible extension: a Comment entity with a required many-to-one relation to Ticket and to User (author), plus created_at — decide the cascade behavior (does deleting a ticket delete its comments?) before generating anything.

Naming and nullability are planning decisions with lasting consequences: a nullable foreign key declares "this relationship is optional" for every query written against that table from then on.

A planning note before generating the migration

Comment
  id uuid pk
  ticket_id uuid fk -> tickets.id, NOT NULL, ON DELETE CASCADE
  author_id uuid fk -> users.id, NOT NULL, ON DELETE SET NULL
  body text NOT NULL
  created_at timestamptz

Every foreign key’s ON DELETE behavior and every column’s nullability should be a stated decision, not an accident of a generator’s defaults.

Keep in mind

Draw or write the ERD first; the migration should encode decisions, not discover them.
Say each relationship out loud with its cardinality and optionality before generating a migration.
Decide every foreign key’s ON DELETE behavior deliberately — it defines what a deletion elsewhere in the graph actually does.
A nullable foreign key is a promise every future query must keep — only make a link optional when it truly is.

Test

Check your understanding

At least 10 questions — mix of concept, syntax, practical, and logic. Score ≥80% (enforced by the API) to save progress.

Checking your session…

15 questions · concept 5 · syntax 3 · practical 4 · logic 3

1. How are users and tickets related in this schema?

Concept

Required one-to-one for every ticketOptional many-to-one assignment via nullable assignee_idMany-to-many with no join tableNo relationship — independent tables

2. What happens when a user is deleted?

Syntax

All of the user’s assigned tickets are deleted (ON DELETE CASCADE)The delete is blocked while any ticket references the user (RESTRICT)assignee_id keeps the old id, now pointing at a missing userassignee_id is set null (ON DELETE SET NULL), tickets remain

3. Why plan ERD before coding endpoints?

Practical

An ERD auto-generates the TypeORM entities for youRelation nullability and delete rules affect API behavior and migrationsThe ERD defines the JSON response envelope shapeERDs are only needed for many-to-many relationships

4. Is every ticket required to have an assignee?

Logic

Yes — the NOT NULL constraint on assignee_id requires oneYes — every many-to-one relation requires a parent rowYes — unless the ticket is soft-deletedNo — assignee_id is nullable

5. What ownership does ManyToOne express?

Concept

Many tickets can reference one userOne ticket can reference many usersEach user references exactly one ticketTickets and users are linked through a join table

6. Why document ON DELETE behavior?

Practical

Callers and admins need to know whether deletes cascade or null outON DELETE only controls the order rows are insertedON DELETE is enforced by ValidationPipe, not the databaseON DELETE determines the response status of a delete request

7. Could tickets and users be fully independent?

Syntax

Not if assignment is a domain feature — you need a relationYes — store the assignee’s name as a plain text column insteadYes — the frontend can join them without a database relationYes — a shared primary key removes the need for a foreign key

8. What belongs on an ERD for this domain?

Logic

The controller routes and their HTTP methodsThe DTO validation rules for each endpointUsers, tickets, keys, and the assignee relationship cardinalityThe Swagger tags and response examples

9. Why nullable assignee is a product choice?

Concept

Nullability is decided automatically by TypeORM defaultsNullability only affects how the column sortsA nullable FK is required for every many-to-one relationUnassigned work is allowed; required assignee would be a different rule

10. Which ERD claim is false for this course?

Practical

Assignee is optional many-to-oneTickets and users have no relationship at allON DELETE SET NULL is usedassignee_id references users.id

11. What product rule does nullable:true encode here?

Conceptintermediate

@ManyToOne(() => User, { nullable: true })
@JoinColumn({ name: 'assignee_id' })
assignee?: User;

Every ticket must have an assigneeA ticket can have many assigneesUsers are required to own a ticketA ticket may be unassigned, so assignee_id can be NULL

12. What is the consequence of CASCADE here for this domain?

Syntaxadvanced

FOREIGN KEY (assignee_id) REFERENCES users(id)
  ON DELETE CASCADE

Deleting a user would also delete all of their tickets, likely losing dataTickets keep their assignee_id after user deletionThe user cannot be deleted while tickets existassignee_id is set to NULL automatically

13. Given users 1 to N tickets via assignee_id, which entity holds the FK?

Practicalintermediate

// users 1 --- N tickets (via assignee_id)

The users table, on the one sideA separate join tableThe tickets table, on the many side of the relationshipBoth tables share the key

14. Which schema fits: a ticket can have many tags and a tag many tickets?

Logicadvanced

// requirement: ticket <-> tag is many-to-many

A nullable FK on ticketsA many-to-many with a ticket_tags join tableA one-to-one between tickets and tagsA JSON column of tag ids with no relation

15. What constraint should the ERD add to email?

Conceptintermediate

CREATE TABLE users (
  id uuid PRIMARY KEY,
  email text
);

A UNIQUE constraint so two users cannot share an emailA second PRIMARY KEY on emailA foreign key to ticketsA NOT NULL on id only

Checking your session…