ER Modeling & Schema Design
A schema is the most expensive code you will ever write. Application code can be refactored in an afternoon; a table with a billion rows and forty services reading it cannot. Most of the long-lived pain in production systems traces back to a modelling decision made in the first week: a relationship assumed to be one-to-one that turned out to be many-to-many, an address stored as a single string, a "type" column that should have been a table.
The Entity-Relationship (ER) model, introduced by Peter Chen in 1976, is the standard tool for getting that first week right. It sits one level above tables: you describe the things in the domain, their properties, and how they relate, and only then translate that picture into relations using a small set of mechanical rules.
This page covers the ER vocabulary as taught in DBMS courses, both common notations, a complete worked example from written requirements to CREATE TABLE statements, and the mapping rules for every construct. It assumes you know what keys and foreign keys are; if not, read The Relational Model & Relational Algebra first.
Where ER Modeling Fits
Database design is traditionally split into three phases:
flowchart LR
R["Requirements: interviews, docs, user stories"] --> C["Conceptual design: ER diagram"]
C --> L["Logical design: relations, keys, constraints"]
L --> N["Refinement: normalization checks"]
N --> P["Physical design: types, indexes, partitioning"]
| Phase | Output | Question it answers | Depends on a specific DBMS? |
|---|---|---|---|
| Conceptual | ER diagram | What things exist and how are they related? | No |
| Logical | Relation schemas with keys and foreign keys | What tables and columns? | Only on "relational" |
| Physical | DDL, indexes, partitions, storage parameters | How is it stored and accessed fast? | Yes |
ER modelling is the conceptual phase. The point of separating it out is that conversations with domain experts happen here, in their vocabulary ("a course can have several sections, each taught by one instructor"), before anyone argues about bigint versus uuid.
Entities and Entity Sets
An entity is a distinguishable thing in the domain: a particular student, a particular course, a particular order. An entity set (or entity type) is the collection of all entities of the same kind: Student, Course, Order. In casual use people say "entity" for both; in diagrams, a rectangle is an entity set.
Each entity set has a key: one or more attributes whose values distinguish its entities. Keys are underlined in Chen notation and marked PK in crow's-foot.
A quick test for whether something should be an entity or merely an attribute: does it have properties of its own, or do other things relate to it? "Department" with a name, a budget and a head is an entity. "Colour" on a T-shirt is an attribute, unless you need colour codes, swatches and translations, in which case it becomes an entity.
Attributes
Attributes describe entities. ER modelling distinguishes several kinds, and each kind maps to tables differently.
| Kind | Meaning | Example | Chen notation | Maps to |
|---|---|---|---|---|
| Simple (atomic) | Cannot be meaningfully divided | age, sid | Oval | One column |
| Composite | Made of sub-parts | name = first + last; address = street + city + postcode | Oval with child ovals | One column per leaf component |
| Single-valued | One value per entity | date_of_birth | Oval | One column |
| Multivalued | Several values per entity | phone_numbers, skills | Double oval | A separate table |
| Derived | Computable from other attributes | age from date_of_birth, order_total from line items | Dashed oval | Usually not stored; a view or generated column |
| Key | Identifies the entity | sid | Underlined name | Primary key |
| Optional / nullable | May be absent | middle_name | Oval (sometimes marked) | Nullable column |
Composite attributes deserve a moment of thought. Storing address as one text column means you cannot query by city without string parsing. Splitting it into street, city, postcode, country costs nothing up front. The general rule: decompose to the level at which you will query, validate or sort.
Multivalued attributes are where beginners most often go wrong. A phone_numbers column holding '555-1234, 555-9876' violates First Normal Form, cannot be indexed per number, and cannot hold a "type" (home, mobile) per number. It becomes its own table:
CREATE TABLE student_phone (
sid integer REFERENCES student (sid) ON DELETE CASCADE,
phone text NOT NULL,
kind text NOT NULL CHECK (kind IN ('mobile','home','work')),
PRIMARY KEY (sid, phone)
);
Arrays and JSON are a physical-design choice, not a modelling one
PostgreSQL can store phone text[] or a jsonb column, and MySQL has JSON. Those are sometimes the right physical choice (tags you only ever read together with the parent). But at the conceptual level the attribute is still multivalued, and you should make that decision consciously: you are trading away per-value constraints, foreign keys and simple joins.
Derived attributes are usually computed rather than stored, because a stored copy can drift out of sync. When you do need to store one (for speed), PostgreSQL 12+ and MySQL 5.7+ support generated columns:
CREATE TABLE order_line (
order_id bigint,
line_no int,
quantity int NOT NULL CHECK (quantity > 0),
unit_price numeric(10,2) NOT NULL,
line_total numeric(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
PRIMARY KEY (order_id, line_no)
);
A generated column can only use values from the same row. A derived attribute spanning rows (order_total as the sum of lines) needs a view, a trigger, or deliberate denormalization.
Relationships
A relationship associates entities: Ada enrolls in CS340. A relationship set is all relationships of one kind: Enrolls. In Chen notation it is a diamond connected to the participating entity sets.
- The degree of a relationship is how many entity sets participate. Binary (two) is by far the most common. Unary or recursive (one entity set playing two roles: Employee manages Employee) is next. Ternary (three: Supplier supplies Part to Project) is rarer and often misused.
- Roles name each participation in a recursive relationship: in manages, one Employee is the
manager, the other thesubordinate. - Relationship attributes belong to the association itself, not to either entity. A grade is not a property of the student (they have many grades) or of the course (it has many grades); it is a property of this student in this course. Grade is an attribute of Enrolls.
Cardinality Ratios
Cardinality says how many entities on one side can be associated with one entity on the other.
| Ratio | Meaning | Example |
|---|---|---|
| 1:1 | Each A relates to at most one B, and vice versa | Employee heads Department (one head per department, one department per head) |
| 1:N | Each A relates to many B; each B to at most one A | Department offers Course |
| N:1 | Mirror of 1:N | Course offered by Department |
| M:N | Many on both sides | Student enrolls in Course |
Participation Constraints
Participation says whether every entity must take part.
- Total participation (mandatory): every Course must be offered by some Department. Shown in Chen notation as a double line; in crow's-foot as a bar
|on the far side. - Partial participation (optional): not every Employee heads a Department. Single line in Chen; circle
oin crow's-foot.
Total participation on the "many" side of a 1:N becomes a NOT NULL foreign key. Partial participation becomes a nullable foreign key. Total participation on the "one" side ("every department must offer at least one course") cannot be expressed with an ordinary constraint; it needs a deferred trigger or application logic.
(min, max) Notation
A more precise alternative writes a pair (min, max) on each edge: the minimum and maximum number of relationships each entity participates in. Student —(0, 6)— Enrolls means each student enrolls in between 0 and 6 courses. Min 0 is partial participation, min ≥ 1 is total; max 1 versus max N gives the cardinality. It removes the ambiguity of "which side does the 1 go on", which differs between textbooks.
Which side does the number go on?
In Chen notation, "Department 1 —— N Course" means one department relates to N courses: the N is written next to Course. In (min, max) notation, the pair is written next to the entity it constrains, so Course gets (1, 1) and Department gets (0, N). These conventions put the numbers on opposite sides. When reading someone else's diagram, check which convention they used before you build tables from it.
Weak Entities
A weak entity set cannot be identified by its own attributes alone. It depends on an owner (identifying) entity set, through an identifying relationship, and has a partial key (discriminator) that is unique only within one owner.
Classic examples:
- A Section of a course: "Section 2" means nothing until you say "of CS340 in Fall 2026". Key: (cid, term, section_no).
- A Room in a building: "Room 101" exists in many buildings. Key: (building_id, room_no).
- An OrderLine: line 3 of order 8812. Key: (order_id, line_no).
- A Dependent of an employee for insurance: identified by employee plus dependent name.
Weak entities always have total participation in the identifying relationship (a section without a course cannot exist), and the identifying relationship is always 1:N from owner to weak entity. In Chen notation, a weak entity is a double rectangle, its identifying relationship a double diamond, and its partial key is underlined with a dashed line.
Mapping is mechanical: the weak entity's table gets the owner's primary key as a foreign key, and its primary key is owner key + partial key. Because the weak entity cannot outlive its owner, the foreign key is usually ON DELETE CASCADE.
CREATE TABLE section (
cid text REFERENCES course (cid) ON DELETE CASCADE,
term text,
section_no smallint,
capacity smallint NOT NULL CHECK (capacity > 0),
PRIMARY KEY (cid, term, section_no)
);
Weak entities and surrogate keys
Many teams give every table a surrogate id column, including weak entities. That is fine physically, but keep the natural identifying key as a UNIQUE (cid, term, section_no) constraint. Otherwise nothing stops two "Section 2 of CS340 in Fall 2026" rows from existing, and you have lost the rule the weak-entity model was capturing.
Enhanced ER: Generalization, Specialization, Aggregation
The original ER model could not express "a graduate student is a kind of student". The Enhanced ER (EER) model adds inheritance and a way to relate relationships.
Specialization and Generalization
Specialization is top-down: start from Person and identify subclasses Student and Instructor, each with extra attributes. Generalization is bottom-up: notice that Car and Truck share many attributes and factor out Vehicle. The result is the same: a superclass and subclasses linked by an IS-A relationship. Subclasses inherit all attributes and relationships of the superclass.
Two independent constraints describe the hierarchy:
| Constraint | Options | Meaning | Example |
|---|---|---|---|
| Disjointness | Disjoint (d) | An entity belongs to at most one subclass | A Vehicle is a Car or a Truck, not both |
| Overlapping (o) | An entity can be in several subclasses | A Person can be both Student and Instructor (a TA) | |
| Completeness | Total | Every superclass entity must be in some subclass | Every Account is Checking or Savings |
| Partial | A superclass entity may be in no subclass | A Person may be neither student nor instructor (staff) |
These two constraints combine freely into four cases, and the case strongly influences which table mapping (below) works best.
Aggregation
Sometimes you need a relationship about a relationship. Suppose an Employee works on a Project, and that assignment (not the employee, not the project) uses certain Machines. ER does not allow a relationship to connect to another relationship directly. Aggregation treats the relationship Works_On together with its entities as a higher-level entity, which can then participate in Uses with Machine.
flowchart LR
subgraph AGG["Aggregation: Works_On treated as an entity"]
direction LR
E["Employee"] --- W{"Works_On"} --- P["Project"]
end
AGG --- U{"Uses"} --- M["Machine"]
In tables, aggregation means the Uses table references the Works_On table's key, which is (emp_id, project_id):
CREATE TABLE works_on (
emp_id bigint REFERENCES employee (emp_id),
project_id bigint REFERENCES project (project_id),
hours numeric(6,1),
PRIMARY KEY (emp_id, project_id)
);
CREATE TABLE uses (
emp_id bigint,
project_id bigint,
machine_id bigint REFERENCES machine (machine_id),
PRIMARY KEY (emp_id, project_id, machine_id),
FOREIGN KEY (emp_id, project_id) REFERENCES works_on (emp_id, project_id)
);
The alternative, a ternary relationship Employee–Project–Machine, would allow a machine to be recorded for an employee on a project they do not work on. Aggregation enforces that the assignment exists first.
Ternary vs Binary Relationships
A ternary relationship is not equivalent to three binary ones. Supplies(Supplier, Part, Project) records "Acme supplies bolts to the Bridge project". Three binaries (Acme supplies bolts; Acme supplies the Bridge project; Bridge uses bolts) lose information: Acme might supply bolts to some other project and something else to Bridge, and the binaries cannot tell you which. Use a ternary when the fact genuinely involves all three at once; otherwise, prefer binaries, which are easier to constrain.
Notations: Chen vs Crow's Foot
There are several ER notations. Textbooks and exams use Chen; industry tools (dbdiagram, Lucidchart, ERwin, most IDEs) use crow's foot (Information Engineering), and UML class diagrams are a third option.
| Concept | Chen | Crow's foot |
|---|---|---|
| Entity set | Rectangle | Rectangle with attribute list inside |
| Weak entity | Double rectangle | Rectangle; key includes the parent's FK (often drawn as an identifying, solid line) |
| Attribute | Oval connected to entity | Row inside the entity box |
| Key attribute | Underlined | PK marker |
| Multivalued attribute | Double oval | Not shown; already mapped to a separate entity |
| Derived attribute | Dashed oval | Not shown, or noted |
| Relationship | Diamond | A line between entities, labelled with a verb |
| Relationship attribute | Oval on the diamond | Becomes an associative entity (junction table) |
| Cardinality | 1, N, M on edges | Line ends: ` |
| Participation | Single (partial) or double (total) line | Inner symbol: o zero (optional), ` |
The crow's-foot line-end symbols, read from the entity outward:
| Symbol | Mermaid syntax (left / right side) | Meaning |
|---|---|---|
| Bar, bar | || | Exactly one |
| Circle, bar | |o / o| | Zero or one |
| Bar, crow's foot | }| / |{ | One or more |
| Circle, crow's foot | }o / o{ | Zero or more |
Chen is more expressive at the conceptual level: it shows multivalued, derived and composite attributes, relationship attributes and n-ary relationships directly. Crow's foot is closer to the logical level: it essentially draws the tables, which is why tools that generate DDL use it. Mermaid's erDiagram is crow's foot.
Worked Example: A University Registration System
Let us design a system end to end.
Step 1: Requirements
The registrar gives us these statements (paraphrased from an interview):
- The university has departments, each with a unique code (e.g.
CS), a name and a budget. - Each department is chaired by exactly one instructor, and an instructor chairs at most one department. Some departments may temporarily have no chair.
- Instructors have an employee ID, a name (first and last), a hire date, and belong to exactly one department. They can have several office phone numbers.
- Students have a student ID, a name, an email (unique), a date of birth, and an optional declared major (a department).
- Students are either undergraduates, who have a class year, or graduate students, who have a thesis title and an advisor (an instructor). Every student is exactly one of these.
- Courses have a code (e.g.
CS340), a title and a number of credits, and are offered by one department. - A course has prerequisite courses.
- Each course is taught in sections per term: "CS340 section 2, Fall 2026". A section has a capacity and a room, and is taught by one or more instructors.
- Students enroll in sections and receive a grade at the end of term.
- We need to show each student's age and GPA.
Step 2: Identify Entities, Attributes, Relationships
Read the requirements for nouns (candidate entities and attributes) and verbs (candidate relationships).
| Element | Kind | Notes |
|---|---|---|
| Department | Entity | Key dept_code |
| Instructor | Entity | Key emp_id; name composite; phones multivalued |
| Student | Entity (superclass) | Key sid; email alternate key; age derived from dob |
| Undergraduate, Graduate | Subclasses of Student | Disjoint, total specialization (rule 5) |
| Course | Entity | Key cid |
| Section | Weak entity, owner Course | Partial key (term, section_no) |
| Chairs | Relationship Instructor–Department | 1:1, partial on both sides |
| Member_of | Relationship Instructor–Department | N:1, total for Instructor |
| Majors_in | Relationship Student–Department | N:1, partial for Student |
| Advises | Relationship Instructor–Graduate | 1:N, total for Graduate |
| Offers | Relationship Department–Course | 1:N, total for Course |
| Prereq_of | Recursive relationship Course–Course | M:N, roles course and prereq |
| Has_section | Identifying relationship Course–Section | 1:N, total for Section |
| Teaches | Relationship Instructor–Section | M:N (rule 8: "one or more instructors") |
| Enrolls | Relationship Student–Section | M:N, with attribute grade |
| GPA | Derived attribute of Student | Computed from Enrolls grades and Course credits |
Note how rule 8's "one or more instructors" turned Teaches into M:N. If the registrar had said "taught by one instructor", it would be 1:N and become a simple foreign key. This is exactly the kind of detail to confirm with the domain expert, because changing it after launch is a migration.
Step 3: The ER Diagram
Chen notation is not supported by Mermaid, so here is the conceptual structure first as a flowchart in Chen style (rectangles for entities, diamonds for relationships, double borders noted in labels), then the crow's-foot version.
flowchart LR
D["DEPARTMENT"]
I["INSTRUCTOR"]
S["STUDENT"]
UG["UNDERGRAD"]
GR["GRADUATE"]
C["COURSE"]
SEC[["SECTION (weak)"]]
CH{"Chairs 1:1"}
MO{"Member_of N:1"}
MJ{"Majors_in N:1"}
OF{"Offers 1:N"}
PR{"Prereq_of M:N"}
HS{"Has_section (identifying)"}
TE{"Teaches M:N"}
EN{"Enrolls M:N, grade"}
AD{"Advises 1:N"}
ISA{"ISA d, total"}
I --- CH --- D
I --- MO --- D
S --- MJ --- D
D --- OF --- C
C --- PR --- C
C --- HS --- SEC
I --- TE --- SEC
S --- EN --- SEC
I --- AD --- GR
S --- ISA
ISA --- UG
ISA --- GR
And the logical, crow's-foot view, which is what you would hand to a developer:
erDiagram
DEPARTMENT ||--o{ INSTRUCTOR : "employs"
DEPARTMENT |o--o| INSTRUCTOR : "chaired by"
DEPARTMENT ||--o{ COURSE : "offers"
DEPARTMENT |o--o{ STUDENT : "majors in"
COURSE ||--o{ SECTION : "has"
COURSE ||--o{ PREREQUISITE : "requires"
COURSE ||--o{ PREREQUISITE : "is required by"
INSTRUCTOR ||--o{ INSTRUCTOR_PHONE : "has"
INSTRUCTOR ||--o{ TEACHES : "teaches"
SECTION ||--|{ TEACHES : "taught by"
STUDENT ||--o{ ENROLLMENT : "enrolls"
SECTION ||--o{ ENROLLMENT : "contains"
STUDENT ||--o| UNDERGRADUATE : "is a"
STUDENT ||--o| GRADUATE : "is a"
INSTRUCTOR ||--o{ GRADUATE : "advises"
DEPARTMENT {
text dept_code PK
text name
numeric budget
int chair_emp_id FK "UNIQUE, nullable"
}
INSTRUCTOR {
int emp_id PK
text first_name
text last_name
date hire_date
text dept_code FK
}
INSTRUCTOR_PHONE {
int emp_id PK, FK
text phone PK
}
STUDENT {
int sid PK
text first_name
text last_name
text email UK
date dob
text major_dept FK "nullable"
}
UNDERGRADUATE {
int sid PK, FK
int class_year
}
GRADUATE {
int sid PK, FK
text thesis_title
int advisor_emp_id FK
}
COURSE {
text cid PK
text title
int credits
text dept_code FK
}
PREREQUISITE {
text cid PK, FK
text prereq_cid PK, FK
}
SECTION {
text cid PK, FK
text term PK
int section_no PK
int capacity
text room
}
TEACHES {
int emp_id PK, FK
text cid PK, FK
text term PK, FK
int section_no PK, FK
}
ENROLLMENT {
int sid PK, FK
text cid PK, FK
text term PK, FK
int section_no PK, FK
text grade
}
Notice the crow's-foot diagram has no diamonds and no multivalued or derived attributes: the M:N relationships have already become junction tables (TEACHES, ENROLLMENT, PREREQUISITE), the multivalued phones have become INSTRUCTOR_PHONE, and age and GPA are gone because they will be computed. That transformation is what the mapping rules do.
Step 4: Map to Tables
The mapping rules, applied in order:
| # | ER construct | Rule |
|---|---|---|
| 1 | Strong entity | One table. Simple attributes become columns; composite attributes become one column per leaf; the key becomes the primary key |
| 2 | Weak entity | One table with the owner's PK as FK (usually ON DELETE CASCADE); PK = owner PK + partial key |
| 3 | 1:1 relationship | FK on one side, with UNIQUE. Put it on the side with total participation if there is one (so it can be NOT NULL) |
| 4 | 1:N relationship | FK on the N side referencing the 1 side. Total participation of the N side means NOT NULL |
| 5 | M:N relationship | New junction table; PK = both FKs; relationship attributes become columns of it |
| 6 | Multivalued attribute | New table: owner PK as FK + the value; PK = both |
| 7 | n-ary relationship (n ≥ 3) | New table with FKs to all participants; PK is usually all of them (or fewer, if some side has cardinality 1) |
| 8 | Recursive relationship | Same as 1:1, 1:N or M:N, with two role-named FK columns to the same table |
| 9 | Specialization | Single-table, class-table or concrete-table (see next section) |
| 10 | Derived attribute | Usually not stored; a view, a generated column, or a computed query |
Applying them:
-- Rule 1: strong entities
CREATE TABLE department (
dept_code text PRIMARY KEY,
name text NOT NULL UNIQUE,
budget numeric(14,2) NOT NULL CHECK (budget >= 0)
-- chair_emp_id added below: instructor does not exist yet
);
CREATE TABLE instructor (
emp_id integer PRIMARY KEY,
first_name text NOT NULL, -- composite name, one column per part
last_name text NOT NULL,
hire_date date NOT NULL,
dept_code text NOT NULL REFERENCES department (dept_code) -- Rule 4, total participation
);
-- Rule 3: Chairs is 1:1, partial on both sides. FK on department, UNIQUE, nullable.
ALTER TABLE department
ADD COLUMN chair_emp_id integer UNIQUE REFERENCES instructor (emp_id) ON DELETE SET NULL;
-- Rule 6: multivalued attribute
CREATE TABLE instructor_phone (
emp_id integer REFERENCES instructor (emp_id) ON DELETE CASCADE,
phone text,
PRIMARY KEY (emp_id, phone)
);
CREATE TABLE course (
cid text PRIMARY KEY,
title text NOT NULL,
credits smallint NOT NULL CHECK (credits BETWEEN 1 AND 6),
dept_code text NOT NULL REFERENCES department (dept_code) -- Rule 4, Offers
);
-- Rule 8 + 5: recursive M:N
CREATE TABLE prerequisite (
cid text REFERENCES course (cid) ON DELETE CASCADE,
prereq_cid text REFERENCES course (cid) ON DELETE CASCADE,
PRIMARY KEY (cid, prereq_cid),
CHECK (cid <> prereq_cid)
);
-- Rule 2: weak entity
CREATE TABLE section (
cid text REFERENCES course (cid) ON DELETE CASCADE,
term text,
section_no smallint,
capacity smallint NOT NULL CHECK (capacity > 0),
room text,
PRIMARY KEY (cid, term, section_no)
);
-- Rule 5: M:N Teaches
CREATE TABLE teaches (
emp_id integer REFERENCES instructor (emp_id),
cid text,
term text,
section_no smallint,
PRIMARY KEY (emp_id, cid, term, section_no),
FOREIGN KEY (cid, term, section_no) REFERENCES section ON DELETE CASCADE
);
-- Rule 9 (class-table inheritance, chosen below) + Rule 4 for Majors_in
CREATE TABLE student (
sid integer PRIMARY KEY,
first_name text NOT NULL,
last_name text NOT NULL,
email text NOT NULL UNIQUE,
dob date NOT NULL,
major_dept text REFERENCES department (dept_code) ON DELETE SET NULL, -- partial
kind text NOT NULL CHECK (kind IN ('UG','GR')) -- disjoint discriminator
);
CREATE TABLE undergraduate (
sid integer PRIMARY KEY REFERENCES student (sid) ON DELETE CASCADE,
class_year smallint NOT NULL CHECK (class_year BETWEEN 1 AND 4)
);
CREATE TABLE graduate (
sid integer PRIMARY KEY REFERENCES student (sid) ON DELETE CASCADE,
thesis_title text,
advisor_emp_id integer NOT NULL REFERENCES instructor (emp_id) -- Rule 4, Advises, total
);
-- Rule 5: M:N Enrolls with relationship attribute grade
CREATE TABLE enrollment (
sid integer REFERENCES student (sid) ON DELETE CASCADE,
cid text,
term text,
section_no smallint,
grade char(2) CHECK (grade IN ('A','A-','B+','B','B-','C+','C','D','F')),
PRIMARY KEY (sid, cid, term, section_no),
FOREIGN KEY (cid, term, section_no) REFERENCES section ON DELETE CASCADE
);
-- Rule 10: derived attributes as a view
CREATE VIEW student_summary AS
SELECT s.sid,
s.first_name || ' ' || s.last_name AS full_name,
date_part('year', age(s.dob))::int AS age,
round(sum(gp.points * c.credits) / nullif(sum(c.credits), 0), 2) AS gpa
FROM student s
LEFT JOIN enrollment e ON e.sid = s.sid AND e.grade IS NOT NULL
LEFT JOIN course c ON c.cid = e.cid
LEFT JOIN (VALUES ('A',4.0),('A-',3.7),('B+',3.3),('B',3.0),('B-',2.7),
('C+',2.3),('C',2.0),('D',1.0),('F',0.0)) AS gp(grade, points)
ON gp.grade = e.grade
GROUP BY s.sid;
MySQL notes: || is logical OR in MySQL by default, so use CONCAT(first_name, ' ', last_name); age() and date_part are PostgreSQL functions, so use TIMESTAMPDIFF(YEAR, dob, CURDATE()); and REFERENCES section without a column list requires naming the columns explicitly in MySQL. The GROUP BY s.sid with other s.* columns in the select list works in PostgreSQL because sid is the primary key (functional dependence); MySQL 5.7+ accepts it for the same reason under ONLY_FULL_GROUP_BY.
What the constraints do not capture. Several rules from the requirements survived only partly:
- "Every student is exactly one of UG or GR" (disjoint, total). The
kindcolumn plus aCHECKgives disjointness on the parent, but nothing forces the matching row inundergraduateorgraduateto exist, or prevents aUGstudent from also getting agraduaterow. Fully enforcing it needs a composite FK trick ((sid, kind)referenced with a fixedkindin each child) or a deferred trigger. - "A section is taught by one or more instructors". The "one or more" (total participation on the many side of an M:N) cannot be a plain constraint.
- Section capacity versus enrollment count spans rows; it needs a trigger or a locked check at enrollment time (see Concurrency Control for why "check then insert" races).
- Prerequisite cycles (CS340 requires CS101 requires CS340) are not prevented by the
CHECK, which only stops a course requiring itself directly.
Being explicit about this gap is part of the design: whatever the schema cannot enforce must be enforced, and tested, somewhere else.
Step 5: Check the Schema with Normalization
After mapping, check each table against the normal forms. A clean ER design usually maps to tables already in 3NF or BCNF, because each entity's attributes depend on its key and nothing else. Problems creep in when an entity was really two entities merged together. For instance, if we had put dept_name and dept_budget on course "for convenience", cid → dept_code → dept_name would be a transitive dependency, a 3NF violation, with the usual update anomalies.
Mapping Specialization: Three Inheritance Strategies
Rule 9 hides a real design decision. Given a superclass with subclasses, there are three standard mappings. They go by different names in different communities (the ORM names, from Martin Fowler's Patterns of Enterprise Application Architecture, are given here).
Running example: Vehicle(vin, make, model, year) with subclasses Car(seats, body_style) and Truck(payload_kg, axles).
Single-Table Inheritance
One table for the whole hierarchy, with a discriminator column and the union of all attributes. Subclass-specific columns are NULL for rows of other types.
CREATE TABLE vehicle (
vin text PRIMARY KEY,
kind text NOT NULL CHECK (kind IN ('car','truck')),
make text NOT NULL,
model text NOT NULL,
year smallint NOT NULL,
seats smallint, -- car only
body_style text, -- car only
payload_kg integer, -- truck only
axles smallint, -- truck only
CHECK (
(kind = 'car' AND seats IS NOT NULL AND payload_kg IS NULL AND axles IS NULL) OR
(kind = 'truck' AND payload_kg IS NOT NULL AND axles IS NOT NULL AND seats IS NULL AND body_style IS NULL)
)
);
Class-Table Inheritance (Table per Type)
One table for the superclass and one per subclass. Each subclass table's primary key is also a foreign key to the superclass. This is what the university example used for Student.
CREATE TABLE vehicle (
vin text PRIMARY KEY,
kind text NOT NULL CHECK (kind IN ('car','truck')),
make text NOT NULL,
model text NOT NULL,
year smallint NOT NULL,
UNIQUE (vin, kind) -- target for the composite FKs below
);
CREATE TABLE car (
vin text PRIMARY KEY,
kind text NOT NULL DEFAULT 'car' CHECK (kind = 'car'),
seats smallint NOT NULL,
body_style text,
FOREIGN KEY (vin, kind) REFERENCES vehicle (vin, kind) ON DELETE CASCADE
);
CREATE TABLE truck (
vin text PRIMARY KEY,
kind text NOT NULL DEFAULT 'truck' CHECK (kind = 'truck'),
payload_kg integer NOT NULL,
axles smallint NOT NULL,
FOREIGN KEY (vin, kind) REFERENCES vehicle (vin, kind) ON DELETE CASCADE
);
The (vin, kind) composite foreign key is the trick for enforcing disjointness: a vehicle whose kind is 'car' cannot have a truck row, because the truck row's (vin, 'truck') would not match. Completeness (every vehicle has a subclass row) still needs a deferred check.
Concrete-Table Inheritance (Table per Concrete Class)
One table per subclass, each repeating the superclass attributes. There is no superclass table.
CREATE TABLE car (
vin text PRIMARY KEY, make text NOT NULL, model text NOT NULL, year smallint NOT NULL,
seats smallint NOT NULL, body_style text
);
CREATE TABLE truck (
vin text PRIMARY KEY, make text NOT NULL, model text NOT NULL, year smallint NOT NULL,
payload_kg integer NOT NULL, axles smallint NOT NULL
);
-- "All vehicles" needs a UNION
CREATE VIEW all_vehicles AS
SELECT vin, make, model, year, 'car' AS kind FROM car
UNION ALL
SELECT vin, make, model, year, 'truck' AS kind FROM truck;
Choosing
| Single-table | Class-table | Concrete-table | |
|---|---|---|---|
| Tables | 1 | 1 + one per subclass | One per subclass |
| Query "all vehicles" | Simple scan | Scan the parent | UNION ALL across tables |
| Query one subclass with all its fields | Filter on kind | One join | Simple scan |
| NULLs | Many | None | None |
| NOT NULL on subclass fields | Only via CHECK per kind | Yes | Yes |
| FK from elsewhere to "any vehicle" | Easy | Easy (to parent) | Impossible with a plain FK |
Unique vin across all types | Automatic | Automatic | Not enforced across tables |
| Overlapping subclasses | Awkward (needs flags, not one kind) | Natural | Duplicates the row |
| Adding a subclass | ALTER TABLE ADD COLUMN | New table | New table, update the view |
| Best when | Few subclasses with few extra columns; you usually query all types | Many subclass attributes; other tables reference the superclass | Subclasses are queried separately and never referenced as a whole |
The default
Start with single-table if subclasses differ by a handful of columns (it is what most ORMs default to and it is the fastest to query). Move to class-table when subclasses have many specific attributes, when NULL-heavy rows become confusing, or when subclass-specific foreign keys appear. Choose concrete-table only when nothing ever needs to reference "a vehicle" generically.
PostgreSQL also has native table inheritance (CREATE TABLE car () INHERITS (vehicle)), but primary keys, unique constraints and foreign keys are not inherited across the hierarchy, which makes it unsuitable for modelling. It survives mostly as the ancestor of declarative partitioning.
War Story (illustrative)
A billing system modelled Payment with single-table inheritance across card, bank transfer, wallet and voucher payments. Over five years it grew to eleven payment kinds and 140 columns, most NULL in any given row. Nobody could tell which columns applied to which kind, and two kinds had started using the same reference column with different meanings. The fix was class-table inheritance: a narrow payment table with the shared fields (amount, currency, status, timestamps) referenced by invoices and refunds, plus one table per method. The lesson was not that single-table is wrong; it was that the hierarchy outgrew it and nobody revisited the choice.
Mapping 1:1 Relationships, in Detail
One-to-one relationships have the most options, and the right one depends on participation.
| Participation | Best mapping | Example |
|---|---|---|
| Total on both sides | Merge into one table | Person and Passport-details-that-every-person-has |
| Total on one side (B must have an A) | FK on B, NOT NULL UNIQUE | Every user_profile belongs to one user |
| Partial on both sides | FK on either side, UNIQUE, nullable; or a separate relationship table | Department chaired by Instructor |
The UNIQUE is what makes it 1:1 rather than 1:N. Forgetting it is a common bug: without it, two departments can quietly share a chair.
Why not always merge 1:1 tables? Reasons to keep them separate: the second entity is optional and wide (avoid many NULLs), it has different access patterns (large bio text read rarely), it has different security (salary in a separate table with tighter grants), or it is owned by a different service.
Common Modelling Mistakes
| Mistake | Symptom | Fix |
|---|---|---|
| Multivalued attribute in one column | tags = 'a,b,c', LIKE '%b%' queries | Separate table (or a deliberate array with a GIN index) |
| M:N modelled as 1:N | "Each product has one category" until it doesn't | Junction table from the start if there is any doubt |
| Relationship attribute put on an entity | grade on student | Attribute belongs to the junction table |
| Ternary split into binaries | Facts that combine three things can't be reconstructed | Keep the ternary, or use aggregation |
| Storing derived values without a plan | order_total disagrees with sum of lines | Compute in a view, or maintain transactionally with a trigger |
1:1 without UNIQUE | Two rows reference the same parent | UNIQUE on the FK |
| Entity hidden in a status string | status = 'shipped via DHL 2026-11-02' | Extract a shipment entity |
Polymorphic FK (owner_type, owner_id) | No FK constraint possible; orphans accumulate | One nullable FK per target with a CHECK that exactly one is set, or a supertype table |
| Weak entity given only a surrogate key | Duplicate "Section 2" rows | Keep UNIQUE on owner key + partial key |
The polymorphic association deserves a note because ORMs (Rails, Laravel) make it easy:
-- Polymorphic: no referential integrity
CREATE TABLE comment_bad (
comment_id bigint PRIMARY KEY,
commentable_type text NOT NULL, -- 'post' or 'photo'
commentable_id bigint NOT NULL, -- references... something
body text NOT NULL
);
-- Exclusive arcs: real FKs, exactly one set
CREATE TABLE comment (
comment_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
post_id bigint REFERENCES post (post_id) ON DELETE CASCADE,
photo_id bigint REFERENCES photo (photo_id) ON DELETE CASCADE,
body text NOT NULL,
CHECK (num_nonnulls(post_id, photo_id) = 1)
);
num_nonnulls is PostgreSQL; in MySQL write CHECK ((post_id IS NOT NULL) + (photo_id IS NOT NULL) = 1).
How This Connects to Normalization
ER modelling and normalization attack the same problem (redundancy and anomalies) from opposite directions:
- ER modelling is top-down. Start from the domain's concepts and derive tables. Good conceptual modelling tends to produce well-normalized tables naturally.
- Normalization is bottom-up. Start from a table (perhaps a spreadsheet someone handed you) and its functional dependencies, and decompose until each table is about one thing.
In practice you use ER to design, then normalization to verify each resulting table. Here is a recap of the normal forms you check against. For the full treatment, with closure computation, Armstrong's axioms and the BCNF decomposition algorithm, see Normalization & Denormalization.
| Normal form | Requirement | Violation example | Fix |
|---|---|---|---|
| 1NF | All attributes atomic; no repeating groups | student(sid, phones = '555-1, 555-2') | Multivalued attribute to its own table |
| 2NF | 1NF, and no non-prime attribute depends on part of a candidate key (no partial dependencies) | enrollment(sid, cid, grade, course_title) with cid → course_title | Move course_title to course |
| 3NF | 2NF, and no non-prime attribute depends on another non-prime attribute (no transitive dependencies). Formally: for every non-trivial X → A, X is a superkey or A is prime | course(cid, dept_code, dept_name) with cid → dept_code → dept_name | Move dept_name to department |
| BCNF | For every non-trivial X → A, X is a superkey (no exception for prime A) | teaches(student, subject, tutor) with tutor → subject, key (student, subject) | Decompose into (tutor, subject) and (student, tutor) |
Two facts worth having ready for interviews:
- 3NF decomposition can always be lossless and dependency-preserving; BCNF decomposition is always lossless but may lose dependencies. In the tutor example, after decomposing, the rule "each student has one tutor per subject" can no longer be checked within a single table.
- Normalization is not the goal; correctness is. Analytical schemas are deliberately denormalized (star schemas, see Dimensional Modeling), and read-heavy OLTP paths sometimes are too. Denormalize consciously, with a plan for keeping the copies consistent, not by accident.
A Second Example in Brief: E-commerce
To show the rules on a different domain, here is a compact e-commerce core. The full production treatment, with inventory, pricing and order state machines, is in E-commerce Schema Design.
Requirements: customers have addresses (several each); products belong to one category and categories nest; an order belongs to one customer, ships to one of their addresses, and has lines each referencing a product at a captured price; a product has variants (size, colour) and each variant has a stock level.
erDiagram
CUSTOMER ||--o{ ADDRESS : "has"
CUSTOMER ||--o{ ORDERS : "places"
ADDRESS ||--o{ ORDERS : "ships to"
ORDERS ||--|{ ORDER_LINE : "contains"
PRODUCT ||--|{ VARIANT : "comes in"
VARIANT ||--o{ ORDER_LINE : "ordered as"
CATEGORY ||--o{ PRODUCT : "groups"
CATEGORY |o--o{ CATEGORY : "parent of"
CUSTOMER {
bigint customer_id PK
text email UK
text full_name
}
ADDRESS {
bigint address_id PK
bigint customer_id FK
text line1
text city
text postcode
text country
}
ORDERS {
bigint order_id PK
bigint customer_id FK
bigint ship_address_id FK
timestamptz placed_at
text status
}
ORDER_LINE {
bigint order_id PK, FK
int line_no PK
bigint variant_id FK
int quantity
numeric unit_price "captured at order time"
}
PRODUCT {
bigint product_id PK
bigint category_id FK
text name
}
VARIANT {
bigint variant_id PK
bigint product_id FK
text sku UK
text size
text colour
int stock
}
CATEGORY {
bigint category_id PK
bigint parent_id FK "nullable"
text name
}
Decisions worth calling out:
- ORDER_LINE is a weak entity of ORDERS (partial key
line_no), and it carriesunit_price, which looks like it duplicates the product price but does not: it is the price at the time of the order, a different fact. Not every repeated value is redundancy; ask what the value means. - Address is a separate entity because it is multivalued for customers and is referenced by orders. But orders often also snapshot the shipping address text, because a customer editing their address should not rewrite history. That is the same "historical fact" argument as
unit_price. - Category is recursive (1:N self-relationship), mapped as a nullable
parent_id. Querying a whole subtree needs a recursive CTE; see Advanced SQL. orders, notorder.ORDERis a reserved word in SQL. Quoting it works but every hand-written query will need the quotes.
Interview Questions
1. What is the difference between a weak entity and a strong entity? Give an example and its table mapping.
A strong entity has its own key; a weak entity is identified only together with its owner, via an identifying relationship, using a partial key. An order line (order_id, line_no) is weak. Its table's primary key is the owner's key plus the partial key, with an FK to the owner, usually ON DELETE CASCADE.
2. How do you map an M:N relationship with attributes to tables?
Create a junction table whose primary key is the two foreign keys, and put the relationship's attributes (grade, quantity, joined_at) on it. If the same pair can relate more than once (a student retaking a course), add a discriminator such as term to the key.
3. Where does the foreign key go in a 1:1 relationship?
On the side with total participation, as NOT NULL UNIQUE. If both sides are total, consider merging the tables. If both are partial, either side works, as UNIQUE and nullable. The UNIQUE is what makes it 1:1.
4. Compare single-table, class-table and concrete-table inheritance.
Single-table: one table with a discriminator, fast, NULL-heavy, subclass NOT NULL needs CHECKs. Class-table: parent plus a table per subclass sharing the PK, clean constraints, needs joins. Concrete-table: one table per subclass repeating parent columns, no generic FKs or cross-type uniqueness. Pick based on how often you query across types and whether other tables reference the superclass.
5. Is a ternary relationship the same as three binary relationships? No. The ternary records facts involving all three at once (supplier S supplies part P to project J); the three binaries cannot reconstruct which combinations actually occurred.
6. What are total and partial participation, and how does each map?
Total: every entity must participate; on the N side of a 1:N it becomes a NOT NULL FK. Partial: participation is optional; a nullable FK. Total participation on the 1 side (each department must have at least one course) cannot be expressed by a simple constraint.
7. What is aggregation in EER, and when do you need it? Treating a relationship and its entities as a single higher-level entity so it can take part in another relationship. You need it when a fact is about an association, e.g. machines used by an employee's assignment to a project. The new relationship's table references the junction table's composite key.
8. Why is the polymorphic association pattern considered an anti-pattern in relational design?
(owner_type, owner_id) cannot be a foreign key, so the database cannot prevent orphans or invalid types. Use one nullable FK per target with a CHECK that exactly one is set, or introduce a common supertype table.
9. A clean ER model maps to tables. Why still check normal forms? Because the conceptual model may have merged two entities, or attributes may hide dependencies the modeller missed (a course table carrying the department name). Normalization verifies each table is about exactly one thing.
10. What is the difference between 3NF and BCNF in one sentence? 3NF allows a non-trivial dependency X → A where X is not a superkey if A is a prime attribute; BCNF does not allow that exception, so every determinant must be a superkey.
Summary
| Concept | One-line version |
|---|---|
| Entity set / relationship set | Rectangles and diamonds: the things and how they associate |
| Attribute kinds | Composite splits into columns, multivalued becomes a table, derived is computed |
| Cardinality | 1:1, 1:N, M:N: how many on each side |
| Participation | Total (must) vs partial (may); maps to NOT NULL vs nullable FK |
| Weak entity | Identified by owner key + partial key; cascades with the owner |
| Specialization | IS-A hierarchy; disjoint/overlapping, total/partial |
| Aggregation | A relationship treated as an entity |
| Chen vs crow's foot | Conceptual vs near-logical; Mermaid draws crow's foot |
| Mapping rules | 1:N → FK on N side; M:N → junction table; 1:1 → FK + UNIQUE |
| Inheritance | Single-table, class-table, concrete-table |
| Normalization | Verifies each mapped table; 3NF/BCNF as the target |
Where to Go Next
- The Relational Model & Relational Algebra: keys, constraints and the algebra your tables will be queried with
- Normalization & Denormalization: functional dependencies, closures and decomposition in full
- Concurrency Control: what happens when many transactions write to the schema at once
- SQL Fundamentals: DDL and querying the tables you designed
- E-commerce Schema Design, SaaS Schema Design, Social Schema Design: production schemas built on these rules
- Dimensional Modeling: when you deliberately model for analytics instead