ER diagrams and database tables: understanding the connection

An ER diagram and a database schema describe the same thing from two distances. The diagram shows what the data is about and how it connects. The schema says exactly how it is stored. People who learn one without the other end up with diagrams that cannot be built or schemas nobody can explain.

This article walks through the mapping piece by piece: entities to tables, attributes to columns, relationships to keys, and the cardinality notation that tells you where each foreign key goes. It ends with a small school database written as SQL and drawn as a diagram, so you can check one against the other.

Entities and tables

The entity-relationship model was introduced by Peter Chen in a 1976 paper, as a way to describe data independently of how any particular database stores it. An entity is a kind of thing the business needs to remember: a customer, an order, a course, a booking. An individual customer is an instance of that entity.

In a relational database, an entity becomes a table and each instance becomes a row. That mapping is close enough that most people treat the words as interchangeable, and in everyday work that is fine. The distinction matters at the edges, which are covered below: some tables are not entities, and some entities need more than one table.

It helps to recognise three levels of ER diagram, because people draw all three and call them all "the ER diagram":

Entity names on a conceptual diagram are usually singular (Customer). Table names on a physical diagram follow whatever convention the codebase uses, often plural and lowercase (customers). Neither is wrong; mixing them on one diagram is.

Attributes and columns

An attribute is a fact about an entity: a customer's email, an order's placed date. On a physical diagram each attribute becomes a column with a type and a nullability rule. The step from one to the other forces decisions the conceptual diagram leaves open.

• Type. "Price" becomes numeric(10,2) or an integer number of pence, and the choice affects rounding everywhere.
• Nullability. Is published_at unknown, or not yet set? A NULL column says the value can be absent; NOT NULL says it never is.
• Uniqueness. An email that must be unique per customer gets a UNIQUE constraint, not just a comment.
• Derived values. An order total can be computed from its lines. Store it only if you have a reason, and if you do, decide which copy wins when they disagree.

Two kinds of attribute do not fit in a single column. A multi-valued attribute, such as a contact's several phone numbers, needs its own table with one row per value. A composite attribute, such as an address, can be split into several columns or moved to its own table if addresses are shared or reused. Both cases are where "one entity, one table" stops being true.

Primary and foreign keys

Keys are how rows find each other. They are also the part of the diagram that maps most directly onto SQL, so it is worth being exact.

A natural key uses real data, such as an ISBN or a student reference. A surrogate key is a generated value, such as an auto-increment integer or a UUID, with no meaning outside the database. Surrogate keys are the common default because real-world identifiers change more often than anyone expects. Keep the natural identifier anyway, as a UNIQUE column, so the database still refuses duplicates.

Every relationship line on a logical ER diagram corresponds to a foreign key column somewhere. If you can draw a line but cannot say which column it represents, the design is not finished.

One-to-one, one-to-many and many-to-many

Relationships are where the ER diagram earns its keep, because the type of relationship decides where the foreign key goes, and that decision is easy to get wrong in SQL and easy to see on a diagram.

One-to-many

The most common case. One author writes many posts; each post has one author. The foreign key goes on the many side: posts.author_id references authors.id. The authors table holds nothing about posts. Putting a list of post ids on the author row is the classic beginner mistake, and it breaks the first time an author has more posts than the list allows.

One-to-one

Each row on one side matches at most one row on the other. Inventory for a product, or a grade for an enrolment. The usual implementation puts the foreign key on the dependent table and makes it the primary key too, which enforces the "at most one" rule without extra constraints. Before drawing a one-to-one, ask whether the two tables should just be one. The answer is often yes, unless the second table is optional, large, or changes on a different schedule.

Many-to-many

A post can have many tags and a tag can apply to many posts. Relational databases cannot store this directly. You need a third table, the join table, holding a foreign key to each side. Its primary key is usually the pair of foreign keys, so the same tag cannot be attached to the same post twice.

When the pairing itself has data, the join table stops being plumbing and becomes an entity. A student enrolled on a course has a term, a withdrawal flag and eventually a grade. That data belongs to neither the student nor the course, so the join table gets a name of its own (enrolments) and, often, its own surrogate key.

Self-referencing relationships

A table can also relate to itself. A comment can reply to another comment; an employee reports to another employee. The foreign key points back at the same table's primary key, and it must be nullable, because the top of the chain (the first comment in a thread, the chief executive) has no parent. On a diagram this is a line that leaves a table and comes back to it, which is easy to miss if the drawing is cramped.

Reading cardinality notation

Cardinality says how many rows on one side relate to a row on the other. Several notations exist. Chen's original notation writes 1, N or M beside diamond-shaped relationship symbols. Most database diagrams today use crow's foot notation, which draws the cardinality directly on the ends of the line.

Read the symbol nearest a table as "how many of this table". On the line between customers and orders, the crow's foot touches orders: one customer, many orders. The outer symbol gives the maximum and the inner one the minimum, so a circle means the relationship is optional on that side.

The minimum translates directly into SQL. A mandatory "exactly one" parent means the foreign key column is NOT NULL. An optional parent means it can be NULL. Simpler diagrams write the cardinality as a label, such as "1 — ∞" for one-to-many, which carries the maximum but not the minimum; the nullability on the foreign key column then tells you the rest.

Worked example: a school enrolment database

A secondary school wants to record which students take which courses each term, and the mark each student gets. Start with the nouns: student, course, term, mark. Student and course are clearly entities. Term and mark describe the pairing of a student with a course, which is the signal that the many-to-many between them needs its own table.

Then decide the keys. Each student already has a school reference number, so that becomes a UNIQUE column, but the primary key is a UUID in case reference formats change. Courses have a code, handled the same way. Enrolments get a surrogate key because a student can take the same course in a different term. A grade belongs to exactly one enrolment and is recorded later, so it lives in its own table with the enrolment's id as both primary and foreign key, which makes the relationship one-to-one.

Here is the result in SQL:

CREATE TABLE students (
  id          uuid PRIMARY KEY,
  reference   varchar(20) NOT NULL UNIQUE,
  name        varchar(120) NOT NULL,
  year_group  int NOT NULL
);

CREATE TABLE courses (
  id       uuid PRIMARY KEY,
  code     varchar(20) NOT NULL UNIQUE,
  title    varchar(200) NOT NULL,
  credits  int NOT NULL
);

CREATE TABLE enrolments (
  id          uuid PRIMARY KEY,
  student_id  uuid NOT NULL REFERENCES students(id),
  course_id   uuid NOT NULL REFERENCES courses(id),
  term        varchar(20) NOT NULL,
  withdrawn   boolean NOT NULL DEFAULT false,
  UNIQUE (student_id, course_id, term)
);

CREATE TABLE grades (
  enrolment_id  uuid PRIMARY KEY REFERENCES enrolments(id),
  mark          int NOT NULL,
  graded_at     timestamp NOT NULL
);

And the same schema drawn. Each table becomes a box with one row per column and key markers beside the names; each REFERENCES clause becomes a line.

Compare the two. The SQL is precise but linear: to see that enrolments joins students to courses, you have to read three statements and hold them together. The diagram puts that structure in one place. The UNIQUE constraint on student, course and term, on the other hand, is only visible in the SQL. Each representation catches things the other hides, which is why it is worth keeping both.

A checklist for reviewing an ER diagram

1. Every table has a primary key, and you can say why that column (or pair) never changes.
2. Every line corresponds to a named foreign key column, on the many side.
3. Every many-to-many has a join table. If you see crow's feet at both ends of one line, it is not finished.
4. Join tables that carry data have been given a proper name.
5. Optional relationships have nullable foreign keys, and mandatory ones do not.
6. Natural identifiers (emails, codes, references) are UNIQUE even when they are not the primary key.
7. No column holds a list. Comma-separated ids in a text column are a missing table.
8. Values that must be preserved at a point in time, such as the price paid on an order line, are stored rather than looked up later.

Working with ER diagrams in DiagramDock

DiagramDock's ER diagram type writes each table as text, one column per line in the form name, type, then any of PK, FK, UNIQUE and NULL; the table renders as rows with key badges. If you already have a schema, "Visualise SQL schema" accepts pasted CREATE TABLE statements and draws the tables. The SQL is read as text only and never executed.

There are six ER templates, including the blog and school schemas above, and ER diagrams can be exported to Mermaid as well as SVG, PNG and PDF. Everything stays in your browser.