ER Diagram Guide: Entities, Keys and Real Examples

November 11, 2026 · 12 min read

ER Diagram Guide: Entities, Relationships and Database Examples

An ER diagram (entity relationship diagram, or ERD) is a picture of the data a system stores: the things it tracks, their attributes and how they connect. This guide builds one course-enrollment database from scratch, draws it as copyable text that renders as a diagram, then turns it into SQL tables.

What Is an ER Diagram?

An entity relationship diagram shows the things a database needs to remember and the links between them. Peter Chen introduced the entity-relationship model in a 1976 paper, "The Entity-Relationship Model: Toward a Unified View of Data", published in ACM Transactions on Database Systems. Most diagrams you see today descend from that idea.

Here's the ER diagram meaning in plain terms. Boxes stand for kinds of things (students, courses), the text inside each box lists facts about that thing, and lines show how the things relate.

You'll reach for one in three situations:

  • Designing a new database, before anyone writes a CREATE TABLE.
  • Onboarding someone to an existing schema, where one diagram beats forty table definitions.
  • Reviewing a change, so the team can see that a new table connects where it should.

Entities, Attributes, Keys and Relationships

Every ER model is made of four building blocks. We'll use a small university system throughout: students enroll in courses, and instructors teach them.

An entity is a kind of thing you store data about, such as STUDENT, COURSE or INSTRUCTOR. One specific student, say Priya with ID 1042, is an instance of that entity. Entity names are nouns, usually singular.

An attribute is a fact about an entity, like a student's name or a course's credit count. Attributes come in four kinds:

  • Simple: one indivisible value, such as credits.
  • Composite: made of parts, such as a full name split into first and last.
  • Multivalued: several values at once, such as a student with two phone numbers.
  • Derived: calculated from other data, such as age from a date of birth.

A primary key is the attribute (or set of attributes) that identifies one instance and never repeats. A foreign key is a column that points at another entity's primary key, and it's how relationships end up in a real database.

Many teams write a short data dictionary next to the diagram. Here's one for STUDENT as a Markdown table (see our Markdown table guide for the syntax):

| Column     | Type         | Key | Note                     |
|------------|--------------|-----|--------------------------|
| student_id | integer      | PK  | Assigned at registration |
| name       | varchar(100) |     | Full legal name          |
| email      | varchar(255) | UK  | University address       |

That renders as a four-column table with a header row and three data rows. PK marks the primary key and UK a unique key. The table gives reviewers the detail a diagram leaves out, such as why a column exists.

What Is a Weak Entity in an ER Diagram?

A weak entity is one that can't be identified by its own attributes alone. It depends on an owner entity, and its key combines the owner's key with a partial key of its own.

Take classrooms. Room 101 exists in the Science building and in the Arts building, so room_number by itself isn't unique. A ROOM only makes sense inside a BUILDING, and you identify it with building_id plus room_number. Here BUILDING is the owner, ROOM is the weak entity and room_number is the partial key.

Delete a building from the database and its rooms have nothing left to belong to. That dependency is the test. In Chen's notation a weak entity gets a double-bordered rectangle, which is the only visual detail you need here.

How Relationships Connect Entities

A relationship describes how instances of two entities pair up. You state it in both directions, because each side can allow a different number of matches.

There are three shapes to know:

  1. One-to-one. Each instructor has one office assignment, and each office assignment belongs to one instructor.
  2. One-to-many. One instructor teaches many courses, but each course has a single instructor of record.
  3. Many-to-many. A student takes many courses, and a course holds many students.

Each side is also optional or mandatory. A course must have an instructor, but a new instructor might teach nothing yet.

Notations differ in how they draw this. Chen's style puts relationships in diamonds and attributes in ovals. Crow's foot style marks the ends of each line, and UML class diagrams write ranges such as 0..* next to the line. The meaning is the same in all three. Our reference to ER diagram symbols decodes the Chen and crow's foot marks one by one.

Conceptual, Logical and Physical ER Models

The same database can be drawn at three levels of detail. Each level answers a different question and suits a different reader.

LevelWhat it showsWho reads itEnrollment example
ConceptualEntities and relationships onlyProduct owners, domain expertsSTUDENT, COURSE and INSTRUCTOR, with lines between them
LogicalAttributes, keys, resolved many-to-many linksDevelopers, analystsENROLLMENT appears, with student_id and course_id as its key
PhysicalTables, column types, constraints, indexes for one database engineDBAs, backend developersgrade varchar(2), a unique index on email

In DBMS courses, an ER model diagram usually means the conceptual or logical level. When a tool reverse-engineers a live database, it produces a physical one. Start at the top and add detail only once the shape is agreed.

How to Design an ER Diagram for a Database, Step by Step

Designing an ER diagram is mostly a conversation about the domain, written down. These steps take the enrollment system from a blank page to a reviewed model.

  1. List the questions the database must answer. For example: which courses is a student taking this term, and who teaches them? What grade did a student get?
  2. Pull out the nouns. Student, course, instructor, grade and term are candidates. Some nouns become entities, others become attributes. Grade turns out to be a fact about an enrollment, not a thing of its own.
  3. Pick a primary key for each entity. Prefer a stable ID over something people change, like an email address.
  4. Draw the relationships and decide how many on each side. Say each one aloud in both directions: one instructor teaches many courses, and each course has one instructor.
  5. Resolve many-to-many links. Student-to-course becomes a new associative entity, ENROLLMENT, that holds one row per student per course. It's also where grade lives.
  6. Add attributes and look for repeating groups. If you catch yourself adding course1, course2, course3 to STUDENT, you've found a missing entity.
  7. Review it with someone who knows the domain. A registrar will spot a missing rule (students can retake a course) faster than any tool.

We prefer starting from the questions the app must answer rather than from the screens. Screens change every release, while the data a university keeps about enrollment barely moves. A model built from questions survives the redesign.

How to Draw an ER Diagram from Text (Mermaid erDiagram)

You don't need a drawing canvas to create an ER diagram. Mermaid's erDiagram type turns a few lines of text into a rendered diagram, so the model can live in a Markdown file next to your code. Here's the enrollment model:

This renders four boxes, each listing its columns with PK, FK or UK tags. A dashed line runs from INSTRUCTOR to COURSE, and solid lines run from STUDENT and COURSE into ENROLLMENT. Each line has a single-instance mark on one end and a many-instances mark on the other.

Read two lines aloud and the syntax stops looking cryptic. INSTRUCTOR ||..o{ COURSE : teaches says each instructor teaches zero or more courses, and each course has exactly one instructor. STUDENT ||--o{ ENROLLMENT : has says a student can have zero or more enrollments, and each enrollment belongs to one student.

The dashed .. line is non-identifying because COURSE has its own key, while the solid -- lines are identifying because ENROLLMENT's key is built from its foreign keys. Mermaid's entity relationship diagram reference lists every other marker and option. Our Mermaid ER diagram syntax guide walks through them with a worked bookshop schema.

Paste the block into the Mermaid editor for a live preview and PNG or SVG export. The same fence renders in our Markdown editor preview, and GitHub draws it in Markdown files, issues and pull requests, per GitHub's diagram docs.

New to fences? Start with our guide to Mermaid diagrams in Markdown. Keep a diagram like this in your repo's README, following our README Markdown guide, so it changes in the same pull request as the schema.

There's a real limitation. Mermaid draws crow's foot style only, so it can't produce Chen diagrams with diamonds and ovals. It's also text-only: there's no drag-and-drop layout, and it won't import a live database or a SQL file. You write the model by hand. Our comparison of free ER diagram tools covers canvas options that handle Chen shapes and SQL import.

Try a cut-down version here. Change o{ to |{ on the INSTRUCTOR line and the COURSE end changes to one or more.

Course enrollment ER diagram

erDiagram
INSTRUCTOR ||..o{ COURSE : teaches
STUDENT ||--o{ ENROLLMENT : has
COURSE ||--o{ ENROLLMENT : includes
STUDENT {
int student_id PK
string name
}
ENROLLMENT {
int student_id PK, FK
int course_id PK, FK
string grade
}

Edit a line or add an entity and the preview redraws.

55 words327 characters19 lines
Markdown

ER Diagram Examples Turned into Database Tables

A finished model maps onto tables with a handful of rules. Each entity becomes a table and each attribute a column. A one-to-many relationship puts a foreign key on the many side. A many-to-many relationship becomes a join table whose primary key combines both foreign keys, and a one-to-one link is a foreign key with a unique constraint. Our ER diagram SQL guide maps each constraint in both directions, starting from existing tables.

Applied to the enrollment model, that gives these tables (PostgreSQL syntax, per the PostgreSQL constraints docs):

CREATE TABLE instructor (
  instructor_id integer PRIMARY KEY,
  name          varchar(100) NOT NULL
);

CREATE TABLE student (
  student_id integer PRIMARY KEY,
  name       varchar(100) NOT NULL,
  email      varchar(255) NOT NULL UNIQUE
);

CREATE TABLE course (
  course_id     integer PRIMARY KEY,
  title         varchar(200) NOT NULL,
  instructor_id integer NOT NULL REFERENCES instructor (instructor_id)
);

CREATE TABLE enrollment (
  student_id integer REFERENCES student (student_id),
  course_id  integer REFERENCES course (course_id),
  grade      varchar(2),
  PRIMARY KEY (student_id, course_id)
);

The NOT NULL on course.instructor_id enforces "every course has an instructor". The composite primary key on enrollment stops the same student enrolling twice in one course. This design can't record a retake, though, which is exactly the kind of rule step 7's review should catch.

Two more ER diagram examples show the same patterns in other domains. A clinic books patients with doctors, and each visit can produce a prescription:

APPOINTMENT is the associative entity between PATIENT and DOCTOR, and the o| end means a visit has zero or one prescription. A library separates the title from the physical copies on the shelf:

Each book has one or more copies, and loans attach to a copy rather than a book. That's how the library knows which copy of a popular title is overdue.

A good ER diagram starts with the questions your data must answer, names the entities and keys, and resolves every many-to-many link before anyone writes SQL. Keep it as text and it stays reviewable in the same pull request as the schema. Paste the enrollment example into the Mermaid editor, swap in your own entities and export a PNG or SVG for your design doc.