Database Design and Modeling

Entity-Relationship Diagrams

Learning Objectives

  • You can read a compact ER diagram notation and recognize the common cardinality symbols.
  • You can draw simple ER diagrams for a design problem.
  • You can read an ER diagram aloud in ordinary sentences.
  • You can use ER diagrams as a communication tool for decisions that have already been made.

ER diagrams give a visual way to communicate the conceptual structure of a database. By this point, you’ve already seen ER diagrams, and in this part, you have already been doing the thinking that an ER diagram captures. The diagram is a way to make the result of that thinking visible.

Diagrams Summarize Information

A diagram makes it easier to see what the main entities are, how they relate, where cardinalities differ, and what the overall scope of the design looks like. ER diagrams are useful for discussion, for design review, and for handing the design off to someone who will implement it.

A useful way to think about an ER diagram is as the output of earlier chapters. The subject-analysis note, the entity-attribute decisions, and the relationship descriptions all feed into the diagram. The diagram does not usually surface new information by itself; it summarizes what the earlier thinking concluded.

When drafting diagrams, do not aim for perfection. If you notice spending more than 10 minutes on a diagram, the idea is probably not yet clear enough to be drawn. Instead, use the diagram as a tool to clarify the design. If the diagram is hard to read aloud in sentences, it probably needs another pass.

Best diagrams are such that you can throw them away and redraw them without losing any of the design thinking. They are tools for thinking that help explore the domain and its questions, but they are not a commitment to a particular structure.

Reading the Notation

Before drawing a diagram, it helps to be able to read one. The course uses a diagram style called Crow’s foot notation, where each line connects two entities and carries two end symbols, one at each end, that describe the cardinality at that end.

The most common end symbols are:

  • || — exactly one on this side (required one),
  • |o — at most one on this side (optional one),
  • o{ — zero or many on this side (optional many),
  • |{ — one or many on this side (required many).

A plain text line is written as A <left-end>--<right-end> B : label, where the left and right ends are filled in with the appropriate symbols. For example:

  • COURSES ||--o{ COURSE_INSTANCES : offered_as says that one course is connected to zero-or-many course instances, and each course instance belongs to exactly one course,
  • USERS ||--o{ ENROLLMENTS : has says that one user may have many enrollments, and each enrollment belongs to exactly one user.

Tools such as Mermaid can turn textual notation into a visual diagram.

Loading Exercise...

A Small Example

Here is a compact ER diagram for the course-platform domain. First, in textual Mermaid notation, and then in a visual diagram.

erDiagram
  COURSES ||--o{ COURSE_INSTANCES : offered_as
  COURSE_INSTANCES ||--o{ EXERCISES : contains
  USERS ||--o{ SUBMISSIONS : makes
  EXERCISES ||--o{ SUBMISSIONS : receives
  USERS ||--o{ ENROLLMENTS : has
  COURSE_INSTANCES ||--o{ ENROLLMENTS : has
Figure 1 — A compact ER diagram for the course-platform domain. Four one-to-many relationships and one many-to-many relationship appear in one picture.

Even this small diagram communicates a lot:

  • courses are offered as specific course instances,
  • course instances contain exercises,
  • users create submissions for exercises,
  • users connect to course instances through an enrollment structure.

That is a lot of conceptual structure in one compact picture. Without the diagram, explaining the same thing in prose takes considerably longer.

Loading Exercise...

Reading a Diagram Aloud

A very helpful design habit is to read an ER diagram aloud in full sentences.

For example, from the diagram above:

  • one course may be offered many times,
  • one course instance may contain many exercises,
  • each submission belongs to one user and one exercise,
  • one user may participate in many course instances, and one course instance may have many enrolled users through enrollment.

If a diagram is hard to read aloud, it is often not yet clear enough. The awkwardness in reading usually points to something that has not been decided: a missing relationship, an unclear cardinality, or an entity that is trying to carry too many roles.

Read it aloud before you trust it

Before sharing a diagram with anyone else, try to read it aloud in sentences. If you find yourself saying “um” or “I think this means,” the diagram probably needs another pass.


Loading Exercise...

Building the Diagram from Earlier Notes

By now, you have a subject-analysis note, a list of entities with attributes, and a description of the relationships. Building the diagram from those pieces is mostly bookkeeping:

  1. draw one box for each entity and list its most important attributes,
  2. draw one line for each relationship, with the cardinality marked on each side,
  3. for many-to-many relationships, draw the linking structure with its own box,
  4. check that every relationship you named in prose has a line on the diagram,
  5. read the diagram aloud in sentences and confirm that every relationship reads cleanly.

The last two steps matter most. It is easy to describe a relationship in words and then forget to draw it, especially for the less obvious ones. Reading the diagram aloud against the prose catches these omissions.

Loading Exercise...

A Second Example: The Recipe App

Here is an ER diagram for the recipe app, again first as text and then as a visual diagram.

erDiagram
  USERS ||--o{ RECIPES : owns
  RECIPES ||--o{ RECIPE_INGREDIENTS : uses
  INGREDIENTS ||--o{ RECIPE_INGREDIENTS : appears_in
  USERS ||--o{ FAVORITES : marks
  RECIPES ||--o{ FAVORITES : is_favorited_by
Figure 2 — An ER diagram for the recipe app. Two many-to-many relationships (recipes-ingredients and users-recipes through favorites) each have their own linking structure.

Read aloud and trace the diagram:

  • one user owns many recipes, and each recipe belongs to one user,
  • one recipe uses many ingredients, and one ingredient appears in many recipes, with quantity stored on the connection,
  • one user can favorite many recipes, and one recipe can be favorited by many users.

Notice how the diagram surfaces the two many-to-many relationships by giving each one its own linking structure. The quantity attribute of RECIPE_INGREDIENTS is a good example of an attribute that belongs to the relationship rather than to either entity on its sides.

A Third Example: The Library

Here is an ER diagram for the library case, again first as text and then as a visual diagram.

erDiagram
  BOOKS ||--o{ COPIES : has
  COPIES ||--o{ LOANS : is_borrowed_as
  MEMBERS ||--o{ LOANS : takes
Figure 3 — An ER diagram for the library. Book and copy are two different entities, and a loan connects a member to a copy at a point in time.

Read aloud and again trace the diagram:

  • one book has many copies, and each copy belongs to one book,
  • one copy has many loans over time, and each loan is for one copy,
  • one member takes many loans, and each loan belongs to one member.

Notice what the diagram makes obvious: there is no direct relationship between a member and a book. The path from a member to a book always goes through a loan and then through a copy. This matches the earlier subject-analysis note exactly.

Keep the Diagram Useful

A good diagram does not need to be huge. It needs to be clear. Clarity usually improves when the diagram:

  • includes only the meaningful entities,
  • shows the central relationships with their cardinality,
  • avoids unnecessary attributes,
  • stays close to the actual problem description.

Clutter comes from including every detail. Clarity comes from choosing which details matter for communication.

A common mistake is to add every attribute of every entity to the diagram, including ones that are obvious from context. The diagram then becomes a spreadsheet. Keep attributes in the diagram only when they are part of the design decision being communicated.


Loading Exercise...

Adding Attributes

Attributes are usually listed inside the box for each entity. They are not connected with lines, and they do not have cardinality symbols. They are just a list of important facts about the entity.

In Mermaid syntax, ER diagrams can include attributes by creating a block for each entity, where the block contains the attributes. For example, the following says that the COURSES entity has two attributes: code and title, both of which are strings.

COURSES {
  string code
  string title
}

The block with attributes is separate from the lines that connect entities. The attributes are part of the entity’s description, but they do not affect the relationships or cardinality.

As an example, the following diagram contains the entities COURSES and COURSE_INSTANCES with their attributes:

erDiagram
  COURSES ||--o{ COURSE_INSTANCES : offered_as
  COURSES {
    string code
    string title
  }
  COURSE_INSTANCES {
    string term
    int year
  }
Figure 4 — A part of an ER diagram for the course management system, containing courses and course instances. Course and course instance are two different entities, and a course instance represents a specific offering of a course.

When reading the diagram aloud, you can say “a course has a code and a title” or “the important attributes of a course are code and title.” The attributes are part of the entity’s description, but they do not affect the relationships or cardinality.

Keys and Conceptual Design

The attributes in an ER diagram are not necessarily the same as the columns in a database table. They are part of the conceptual design, not the implementation design. The diagram is about the domain, not about SQL.

For example, the code attribute of COURSES might be a unique identifier for a course, but it is not necessarily the primary key in the database. The diagram does not specify how the database will be structured, only what the important facts about courses are.


Loading Exercise...

Check Your Understanding

  1. What do the symbols || and o{ mean on the two ends of a relationship line?
  2. Why is it useful to read an ER diagram aloud in ordinary language?
  3. Why is the first ER diagram rarely the final one, and why does a many-to-many relationship usually deserve its own visible structure in the diagram?
← Relationships, Cardinality, and Optionality
Testing a Design Against Its Questions →