What Is an ER Diagram?

An entity-relationship diagram is a picture of the entities in a system, their attributes and the relationships between them. Peter Chen introduced the notation in 1976, and its enduring usefulness comes from one property: you cannot draw a relationship without deciding its cardinality.

The three parts

• Entities — the things you store. A customer, an order, a product. Usually a table.
• Attributes — what you know about them.
• Relationships — how they connect, and critically *how many* on each side.

Cardinality is where the design happens

Reading the notation matters less than answering the questions it forces. Each of these is a product decision disguised as a data question, and each one is expensive to change later:

• Can a customer have zero orders? (Almost always yes — so the relationship is optional.)
• Can an order have zero line items? (Usually no — so enforce it.)
• Can a product appear on many orders? (Yes — so you need a join table, not a foreign key.)
• Can a user belong to more than one organisation? (The answer determines whether you have a straightforward schema or a multi-tenancy problem.)

Many-to-many needs a table with its own facts

-- A student takes many courses; a course has many students.
-- The join table is not plumbing - it carries facts of its own.
CREATE TABLE enrolment (
  student_id  bigint      NOT NULL REFERENCES student(id) ON DELETE CASCADE,
  course_id   bigint      NOT NULL REFERENCES course(id)  ON DELETE RESTRICT,
  enrolled_at timestamptz NOT NULL DEFAULT now(),
  grade       text,                       -- belongs to the relationship
  PRIMARY KEY (student_id, course_id)
);

`grade` is a property of the enrolment, not of the student or the course. Spotting that a relationship has its own attributes is one of the clearest signals the model is being taken seriously.

Logical and physical

• Conceptual — entities and relationships only. The version to draw with non-engineers, because it is about the domain rather than the database.
• Logical — attributes, keys, normalisation applied. Still database-agnostic.
• Physical — actual types, indexes, constraints. What you implement.

Most teams jump straight to physical and lose the conversation the conceptual diagram would have produced — which is where the cardinality mistakes get caught cheaply.

Keeping it honest

A diagram that drifts from the schema is worse than none, because people trust it. Generate it from the live schema where you can, and treat any hand-drawn version as a conversation artefact with a date on it rather than as documentation.