Module 15 · Data modelling · Lesson 5 of 5
From ER diagrams to tables
The mechanical rules for turning an ER design into relations: strong and weak entities, composite and multivalued attributes, 1:1, 1:N and M:N relationships, total participation, and the three ways to store a specialisation hierarchy.
Why it matters
An ER diagram is only useful if it becomes a correct schema, and the translation follows a small set of rules that exams test directly and that every schema review relies on. Getting it wrong produces the classic schema bugs: a many-to-many relationship squeezed into one table, a foreign key on the wrong side, or an inheritance hierarchy that forces half the columns to be NULL. Learning the rules once makes schema design fast and defensible.
The idea
Each ER construct maps to relations by a fixed rule. Work through the diagram in order: strong entities, weak entities, relationships by type, multivalued attributes, then hierarchies.
| ER construct | Relational mapping |
|---|---|
| Strong entity | A table with its simple attributes; the entity's key becomes the primary key |
| Composite attribute | One column per component (first_name, last_name); the composite itself disappears |
| Derived attribute | Usually no column; compute it, or store it with care if expensive |
| Multivalued attribute | A separate table (owner_key, value) with the pair as primary key |
| Weak entity | A table containing the owner's key as a foreign key; primary key = owner key + partial key; ON DELETE CASCADE from the owner |
| 1:1 relationship | Foreign key on either side, preferably the side with total participation, made UNIQUE; or merge the two tables if both participations are total |
| 1:N relationship | Foreign key on the N side referencing the 1 side; relationship attributes go on the N side |
| M:N relationship | A new junction table holding both keys as foreign keys, plus relationship attributes; primary key = both keys (or a surrogate plus UNIQUE on both) |
| n-ary relationship | A table holding keys of all participants |
The many-to-many case
A many-to-many relationship can never be represented by a column in either entity's table: a student would need a list of subjects, which breaks atomicity, or the student row would be duplicated per subject, which breaks everything else. The relationship becomes a table of its own.
Participation becomes constraints
Total participation on the side holding a foreign key becomes NOT NULL on that column: if every professor must belong to a department, professor.dept_id is NOT NULL. Partial participation leaves it nullable. Total participation on the other side ("every department must have at least one professor") cannot be expressed with a simple constraint; it needs a deferred check, a trigger or application logic, which is why many schemas quietly leave it out.
Mapping a specialisation hierarchy
| Strategy | Tables | Pros | Cons |
|---|---|---|---|
| Single table | One table for the whole hierarchy with a type column; subtype columns nullable | No joins; simple queries across all types | Many NULLs; subtype NOT NULL rules cannot be declared simply |
| Table per subtype (class table) | A table for the supertype plus one per subtype sharing its primary key | Normalized; constraints per subtype | Joins to assemble a full entity |
| Table per concrete subtype | One table per subtype, each repeating the shared columns | No joins for one subtype | Queries across all types need UNION; shared uniqueness hard to enforce |
Choose by how the data is queried: mostly as a whole (single table), mostly by subtype with distinct rules (table per subtype), or subtypes that rarely mix (concrete tables). Overlapping specialisations fit the table-per-subtype form best, since one entity can have rows in several subtype tables.
A worked example
Library requirements: books have ISBN, title and several authors; the library owns copies of each book numbered 1, 2, 3 within the book; members borrow copies with a due date.
- 1
Strong entities
Book(isbn PK, title), Author(author_id PK, name), Member(member_id PK, name, email UNIQUE). - 2
Weak entity
Copy(isbn FK, copy_no, shelf), PK (isbn, copy_no), ON DELETE CASCADE from Book. - 3
M:N relationship
BookAuthor(isbn FK, author_id FK), PK (isbn, author_id). - 4
Relationship with history
Loan(loan_id PK, isbn, copy_no, member_id FK, borrowed_at, due_at, returned_at), with (isbn, copy_no) a foreign key to Copy. A partial unique index on (isbn, copy_no) where returned_at is NULL allows only one active loan per copy.
Trade-offs
Following the rules strictly yields a normalized, constraint-rich schema that is easy to reason about, at the cost of more tables and joins. Deviations (merging 1:1 tables, single-table inheritance, storing a derived value) trade normalization for simpler or faster queries, and are fine as conscious decisions with the consequences named. The next module, normalization, gives the formal tool for checking whether a mapping introduced redundancy.
In an interview
State the rule as you apply it: "Member to Loan is one-to-many, so loans hold member_id; Book to Author is many-to-many, so a BookAuthor junction table with a composite primary key; Copy is weak, keyed by isbn plus copy number." Interviewers listen for whether you know where the foreign key goes and why many-to-many needs its own table.
Check yourself
In a 1:N relationship 'a department employs many professors, each professor works in exactly one department', where does the foreign key go, and what constraint expresses 'exactly one'?