Skip to main content

ER Modeling and Translation to Tables

What This Concept Is​

Entity-Relationship (ER) modeling is a mid-level design notation between informal English and concrete DDL. It separates what exists in the domain from how we store it.

Core vocabulary:

  • Entity set: a type of thing we track (Student, Course, Hotel).
  • Attribute: a fact about an entity (name, email). Attributes can be simple, composite (address = {street, city}), or multivalued (phone numbers).
  • Relationship set: an association between entity sets (enrolls(Student, Course)).
  • Cardinality: 1:1, 1:N, N:M, plus participation (total vs partial).
  • Identifying attributes: the ones that form the entity's key.
  • Weak entity: one that has no key of its own; identified only via its relationship to a strong entity (LineItem inside an Order).

Translation to relational tables is mostly mechanical once the ER diagram is right:

  • Strong entity -> table with its attributes and a primary key.
  • Weak entity -> table whose primary key is the strong entity's key plus the weak entity's discriminator.
  • 1:N relationship -> foreign key on the "many" side pointing to the "one" side. No separate table.
  • M:N relationship -> junction table whose primary key is the composite of both foreign keys (plus any relationship attributes, e.g. enrollment grade).
  • 1:1 relationship -> foreign key on either side, with a UNIQUE constraint on it.
  • Multivalued attribute -> separate table keyed by entity id + value.

Why It Matters Here​

  • jumping from English straight to DDL skips the step where the ambiguity lives; ER modeling forces you to name entities, choose cardinalities, and notice missing pieces
  • most "my schema is weird" complaints trace back to a missing M:N junction or a confused 1:1 that should have been an attribute
  • 3NF decomposition (Concept 7) and ER translation tend to converge on the same schema when you do both carefully; disagreement is a signal to re-examine both

Concrete Example​

Domain: a course registration system. A Student can enroll in many Courses; each Course has many Students; each enrollment has a grade once the term ends; each Course is taught in a given Semester (1:N between Semester and Course).

Translation:

CREATE TABLE semester(
    id    SERIAL PRIMARY KEY,
    label TEXT NOT NULL UNIQUE
);

CREATE TABLE course(
    code        TEXT PRIMARY KEY,
    title       TEXT NOT NULL,
    semester_id INT NOT NULL REFERENCES semester(id)
);

CREATE TABLE student(
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

-- M:N becomes a junction table.
CREATE TABLE enrollment(
    student_id  INT  NOT NULL REFERENCES student(id),
    course_code TEXT NOT NULL REFERENCES course(code),
    grade       TEXT,
    PRIMARY KEY (student_id, course_code)
);

Expected end state: joining these tables reconstructs everything in the domain, and each fact is stored in exactly one place (it is also in 3NF).

Common Confusion / Misconception​

"M:N relationships can live without a junction table." Not without denormalization. If you try to store "list of course codes" inside student, you break 1NF and every query that asks "who is in CS101?" becomes a scan.

"A 1:1 relationship is always one table." Only if participation is total on both sides and the two halves share identity. If a User may or may not have a Profile, a separate profile table with a UNIQUE FK to user(id) is cleaner and avoids wide rows full of NULLs.

"Weak entities are an obsolete complication." They are often the cleanest model for "a thing that only exists inside another thing" (order line items, hotel rooms, section enrollments). The giveaway is "this has no meaning on its own, only relative to its parent."

How To Use It​

For every new module of an application:

  1. List the nouns in the user-facing description. Candidate entities.
  2. List the verbs between them. Candidate relationships.
  3. For each relationship, ask: can a thing on one side be related to many things on the other side? Do this in both directions to fix cardinality.
  4. Draw the diagram. Do not skip this step even for small schemas.
  5. Translate mechanically using the rules above.
  6. Re-check each resulting table against 3NF (Concept 7) as a backstop.

Check Yourself​

  1. How do you translate a ternary M:N:N relationship into tables?
  2. Why does a weak entity's table usually have a composite primary key?
  3. When does a 1:1 relationship become just an extra column?

Mini Drill or Application​

Model one of these domains end-to-end: (a) a library with books, copies, members, and loans; (b) a hospital with patients, doctors, appointments, and prescriptions; (c) a small e-commerce system with customers, products, orders, and order items. Produce: ER diagram, DDL with all keys and foreign keys, and two non-trivial SQL queries each.

Read This Only If Stuck​