Skip to content

ERD with relational schema

Design question 3: How is data organised and stored?

Entities, keys and relationships, then the tables.

What it explains and when it is useful

An entity relationship diagram (ERD) shows the things a database stores, their attributes and keys, and how they relate (one-to-many, many-to-many). The relational schema lists the actual tables.

When to use it: Any solution that uses a relational database (SQLite, MySQL, PostgreSQL, SQL Server).

Worked example

Illustrative: PracticePal is a fictional project used throughout this guide.

ERD for PracticePal in crow's foot notation: STUDENT, BOOKING, ROOM, WAITLIST_ENTRY, NEIGHBOUR and ROOM_BLOCK with primary and foreign keys; for example a student makes zero or many bookings and each booking is for exactly one roomSTUDENTPKstudent_idnameemailpassword_hashroleBOOKINGPKbooking_idFKstudent_idFKroom_codestart_timeend_timeloudstatusROOMPKroom_codenameloud_allowedWAITLIST_ENTRYPKentry_idFKstudent_idFKroom_codeslot_startjoined_atstateNEIGHBOURPK,FKroom_aPK,FKroom_bROOM_BLOCKPKblock_idFKroom_codefrom_dateto_datereasonmakesis forwaits forforroom_aroom_bblocked byAt each end of a line: the symbol touching the entity = maximum (bar one, crow's foot many); the outer symbol = minimum (bar one, circle zero).
PracticePal's ERD in crow's foot notation.

Scroll sideways to see the whole diagram.

Relational schema

STUDENT(student_id, name, email, password_hash, role)
ROOM(room_code, name, loud_allowed)
NEIGHBOUR(room_a*, room_b*)                 -- one row per shared wall
BOOKING(booking_id, student_id*, room_code*, start_time, end_time, loud, status)
        UNIQUE(room_code, start_time)
WAITLIST_ENTRY(entry_id, student_id*, room_code*, slot_start, joined_at, state, offered_at)
ROOM_BLOCK(block_id, room_code*, from_date, to_date, reason)

underlined = primary key     * = foreign key

The many-to-many “room is next to room” relationship becomes the link table NEIGHBOUR. Field types, sizes and validation are in the data dictionary.

Notation and legend

Symbol or partMeaning
BoxEntity (becomes a table)
PK / FKPrimary key / foreign key
||Exactly one
o|Zero or one
|< (crow's foot + bar)One or many
o< (crow's foot + circle)Zero or many

Design decisions it communicates

A diagram earns marks when you explain the decisions it shows. In PracticePal's documentation these would each be one sentence:

  • A UNIQUE constraint on (room_code, start_time) backs up the clash check (SC4).
  • NEIGHBOUR is a link table, so rooms can be re-arranged without changing code (SC6).
  • Waiting-list entries are rows, not a list in memory, so the queue survives a restart (SC7).
  • The schema is in 3NF: no repeated student or room details in BOOKING.

Common mistakes

  • Leaving many-to-many relationships without a link table.
  • No keys shown.
  • Methods inside entities (that is a class diagram).
  • Cardinality symbols at the wrong ends.
Often confused with: Class diagram. If your boxes have methods, it is a class diagram. If they have keys and cardinality, it is an ERD. Name each diagram correctly.

Checklist for your own version

Ticks are saved in this browser only, so you can use the list while you draft.