Stop 06 · SQL DatabasesTopics · 13/14

Database Design & ER Diagrams

In short

Changing a database design after launch is painful. Ten minutes sketching boxes and lines first can save you weeks of migrations later.

The process of planning your tables, columns, relationships and constraints before writing any code — Entity-Relationship diagrams are the standard way to visualize this.

The blob in a hard hat draws joined boxes on a blueprint next to an empty plot.

Think of it like this

The blueprint before the bricks

No mistri starts laying bricks without a plan for rooms, doors and pipes. An ER diagram is that blueprint for data: boxes for things (entities) and lines for how they connect (relationships). Moving a line on paper is free; moving a wall isn't.

Watch it happen

From a sentence to tables

Nouns

Entities

Attributes

Relations

Tables

1/5Write the feature in one sentence: students enrol in courses taught by teachers. Underline the nouns.

The big ideas

What you need to know

Entities and attributes

An entity is a thing you store, like Student. Attributes are its facts, like name and email.

Cardinality

One-to-one, one-to-many or many-to-many. It decides where the foreign key goes.

Junction tables

Many-to-many needs a middle table holding both ids, often with extras like enrolled_at.

Draw it first

Use paper, dbdiagram.io or draw.io. Share it with your team before writing any CREATE TABLE.

See it in code

Walk through the code

sql

1CREATE TABLE teachers (id SERIAL PRIMARY KEY, name TEXT NOT NULL);2CREATE TABLE students (id SERIAL PRIMARY KEY, name TEXT NOT NULL);3 4CREATE TABLE courses (             -- one teacher, many courses5  id         SERIAL PRIMARY KEY,6  title      TEXT NOT NULL,7  teacher_id INT NOT NULL REFERENCES teachers(id)8);9 10CREATE TABLE enrollments (         -- many students, many courses11  student_id  INT REFERENCES students(id),12  course_id   INT REFERENCES courses(id),13  enrolled_at TIMESTAMPTZ DEFAULT now(),14  PRIMARY KEY (student_id, course_id)15);

1/4Simple entities first. Each gets its own table and id.

Read it back as sentences: a teacher teaches many courses; a student takes many courses. If a sentence sounds wrong, the design is wrong.

Save yourself the pain

Common mistakes

Oops

Putting a course_ids list column on students.

Do this instead

Many-to-many always becomes a junction table. A list in a column can't be joined or constrained.

Oops

Designing tables straight from your UI screens.

Do this instead

Design from the real-world things and their links. Screens change every sprint; entities rarely do.

Your mission

Design a library

~30 min

Quick check

Doctors see many patients, and patients see many doctors. How do you store this?

Go deeper

1Draw an ER diagram for a small app (e.g. an e-commerce store) before building it.
2Practice identifying one-to-many and many-to-many relationships and how to model each.
3Review a real open-source project's database schema for design patterns.