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.)
The question that saves the most rework
Can this ever be more than one? Teams model one-to-one because today there is one — one address, one payment method, one owner. Every later requirement to have two is a migration, a backfill and a rewrite of every query that assumed singular. Ask it explicitly for every relationship, and record the answer.
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.
Frequently Asked Questions
Which notation should I use?
Crow's foot is the most widely recognised for cardinality and the easiest to read without a legend. Consistency within a team matters more than the choice.
Is an ER diagram useful for NoSQL?
The entities and relationships still exist; what changes is how you store them. Drawing the relationships often reveals that a document model will duplicate a fact that ought to be singular.
Should diagrams live in the repository?
Yes, as text you can diff - Mermaid, PlantUML or DBML. A PNG in a wiki is out of date within a month and nobody can see what changed.
References
- PostgreSQL Documentation — Data Definition — PostgreSQL
- PostgreSQL Documentation — Constraints — PostgreSQL
About Jishu Labs
Jishu Labs is a software development company founded in 2016. We build custom software, AI/ML systems, and full-stack web and mobile applications for clients, and we make eight AI tools for software teams.