On this page

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"]
PhaseOutputQuestion it answersDepends on a specific DBMS?
ConceptualER diagramWhat things exist and how are they related?No
LogicalRelation schemas with keys and foreign keysWhat tables and columns?Only on "relational"
PhysicalDDL, indexes, partitions, storage parametersHow 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.

KindMeaningExampleChen notationMaps to
Simple (atomic)Cannot be meaningfully dividedage, sidOvalOne column
CompositeMade of sub-partsname = first + last; address = street + city + postcodeOval with child ovalsOne column per leaf component
Single-valuedOne value per entitydate_of_birthOvalOne column
MultivaluedSeveral values per entityphone_numbers, skillsDouble ovalA separate table
DerivedComputable from other attributesage from date_of_birth, order_total from line itemsDashed ovalUsually not stored; a view or generated column
KeyIdentifies the entitysidUnderlined namePrimary key
Optional / nullableMay be absentmiddle_nameOval (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 the subordinate.
  • 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.

RatioMeaningExample
1:1Each A relates to at most one B, and vice versaEmployee heads Department (one head per department, one department per head)
1:NEach A relates to many B; each B to at most one ADepartment offers Course
N:1Mirror of 1:NCourse offered by Department
M:NMany on both sidesStudent 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 o in 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:

ConstraintOptionsMeaningExample
DisjointnessDisjoint (d)An entity belongs to at most one subclassA Vehicle is a Car or a Truck, not both
Overlapping (o)An entity can be in several subclassesA Person can be both Student and Instructor (a TA)
CompletenessTotalEvery superclass entity must be in some subclassEvery Account is Checking or Savings
PartialA superclass entity may be in no subclassA 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.

ConceptChenCrow's foot
Entity setRectangleRectangle with attribute list inside
Weak entityDouble rectangleRectangle; key includes the parent's FK (often drawn as an identifying, solid line)
AttributeOval connected to entityRow inside the entity box
Key attributeUnderlinedPK marker
Multivalued attributeDouble ovalNot shown; already mapped to a separate entity
Derived attributeDashed ovalNot shown, or noted
RelationshipDiamondA line between entities, labelled with a verb
Relationship attributeOval on the diamondBecomes an associative entity (junction table)
Cardinality1, N, M on edgesLine ends: `
ParticipationSingle (partial) or double (total) lineInner symbol: o zero (optional), `

The crow's-foot line-end symbols, read from the entity outward:

SymbolMermaid 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):

  1. The university has departments, each with a unique code (e.g. CS), a name and a budget.
  2. Each department is chaired by exactly one instructor, and an instructor chairs at most one department. Some departments may temporarily have no chair.
  3. 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.
  4. Students have a student ID, a name, an email (unique), a date of birth, and an optional declared major (a department).
  5. 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.
  6. Courses have a code (e.g. CS340), a title and a number of credits, and are offered by one department.
  7. A course has prerequisite courses.
  8. 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.
  9. Students enroll in sections and receive a grade at the end of term.
  10. 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).

ElementKindNotes
DepartmentEntityKey dept_code
InstructorEntityKey emp_id; name composite; phones multivalued
StudentEntity (superclass)Key sid; email alternate key; age derived from dob
Undergraduate, GraduateSubclasses of StudentDisjoint, total specialization (rule 5)
CourseEntityKey cid
SectionWeak entity, owner CoursePartial key (term, section_no)
ChairsRelationship Instructor–Department1:1, partial on both sides
Member_ofRelationship Instructor–DepartmentN:1, total for Instructor
Majors_inRelationship Student–DepartmentN:1, partial for Student
AdvisesRelationship Instructor–Graduate1:N, total for Graduate
OffersRelationship Department–Course1:N, total for Course
Prereq_ofRecursive relationship Course–CourseM:N, roles course and prereq
Has_sectionIdentifying relationship Course–Section1:N, total for Section
TeachesRelationship Instructor–SectionM:N (rule 8: "one or more instructors")
EnrollsRelationship Student–SectionM:N, with attribute grade
GPADerived attribute of StudentComputed 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 constructRule
1Strong entityOne table. Simple attributes become columns; composite attributes become one column per leaf; the key becomes the primary key
2Weak entityOne table with the owner's PK as FK (usually ON DELETE CASCADE); PK = owner PK + partial key
31:1 relationshipFK on one side, with UNIQUE. Put it on the side with total participation if there is one (so it can be NOT NULL)
41:N relationshipFK on the N side referencing the 1 side. Total participation of the N side means NOT NULL
5M:N relationshipNew junction table; PK = both FKs; relationship attributes become columns of it
6Multivalued attributeNew table: owner PK as FK + the value; PK = both
7n-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)
8Recursive relationshipSame as 1:1, 1:N or M:N, with two role-named FK columns to the same table
9SpecializationSingle-table, class-table or concrete-table (see next section)
10Derived attributeUsually 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 kind column plus a CHECK gives disjointness on the parent, but nothing forces the matching row in undergraduate or graduate to exist, or prevents a UG student from also getting a graduate row. Fully enforcing it needs a composite FK trick ((sid, kind) referenced with a fixed kind in 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-tableClass-tableConcrete-table
Tables11 + one per subclassOne per subclass
Query "all vehicles"Simple scanScan the parentUNION ALL across tables
Query one subclass with all its fieldsFilter on kindOne joinSimple scan
NULLsManyNoneNone
NOT NULL on subclass fieldsOnly via CHECK per kindYesYes
FK from elsewhere to "any vehicle"EasyEasy (to parent)Impossible with a plain FK
Unique vin across all typesAutomaticAutomaticNot enforced across tables
Overlapping subclassesAwkward (needs flags, not one kind)NaturalDuplicates the row
Adding a subclassALTER TABLE ADD COLUMNNew tableNew table, update the view
Best whenFew subclasses with few extra columns; you usually query all typesMany subclass attributes; other tables reference the superclassSubclasses 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.

ParticipationBest mappingExample
Total on both sidesMerge into one tablePerson and Passport-details-that-every-person-has
Total on one side (B must have an A)FK on B, NOT NULL UNIQUEEvery user_profile belongs to one user
Partial on both sidesFK on either side, UNIQUE, nullable; or a separate relationship tableDepartment 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

MistakeSymptomFix
Multivalued attribute in one columntags = 'a,b,c', LIKE '%b%' queriesSeparate table (or a deliberate array with a GIN index)
M:N modelled as 1:N"Each product has one category" until it doesn'tJunction table from the start if there is any doubt
Relationship attribute put on an entitygrade on studentAttribute belongs to the junction table
Ternary split into binariesFacts that combine three things can't be reconstructedKeep the ternary, or use aggregation
Storing derived values without a planorder_total disagrees with sum of linesCompute in a view, or maintain transactionally with a trigger
1:1 without UNIQUETwo rows reference the same parentUNIQUE on the FK
Entity hidden in a status stringstatus = 'shipped via DHL 2026-11-02'Extract a shipment entity
Polymorphic FK (owner_type, owner_id)No FK constraint possible; orphans accumulateOne nullable FK per target with a CHECK that exactly one is set, or a supertype table
Weak entity given only a surrogate keyDuplicate "Section 2" rowsKeep 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 formRequirementViolation exampleFix
1NFAll attributes atomic; no repeating groupsstudent(sid, phones = '555-1, 555-2')Multivalued attribute to its own table
2NF1NF, and no non-prime attribute depends on part of a candidate key (no partial dependencies)enrollment(sid, cid, grade, course_title) with cid → course_titleMove course_title to course
3NF2NF, 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 primecourse(cid, dept_code, dept_name) with cid → dept_code → dept_nameMove dept_name to department
BCNFFor 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:

  1. 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.
  2. 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 carries unit_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, not order. ORDER is 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

ConceptOne-line version
Entity set / relationship setRectangles and diamonds: the things and how they associate
Attribute kindsComposite splits into columns, multivalued becomes a table, derived is computed
Cardinality1:1, 1:N, M:N: how many on each side
ParticipationTotal (must) vs partial (may); maps to NOT NULL vs nullable FK
Weak entityIdentified by owner key + partial key; cascades with the owner
SpecializationIS-A hierarchy; disjoint/overlapping, total/partial
AggregationA relationship treated as an entity
Chen vs crow's footConceptual vs near-logical; Mermaid draws crow's foot
Mapping rules1:N → FK on N side; M:N → junction table; 1:1 → FK + UNIQUE
InheritanceSingle-table, class-table, concrete-table
NormalizationVerifies each mapped table; 3NF/BCNF as the target

Where to Go Next