Skip to main content

2  Designing with ERDs

IntroLearning Objectives

After studying this chapter, you will be able to:

  • recognize the entities, attributes, and relationships in a description of an organization;
  • determine the minimum and maximum cardinality of each relationship;
  • draw and read an ERD in crow’s foot notation;
  • resolve a many-to-many relationship with an associative entity;
  • convert an ERD into tables with primary and foreign keys using a fixed set of rules.

In Chapter 1 you saw that the design of the online store consists of four tables. In this chapter you will learn how to arrive at such a design yourself. The tool for this is the entity relationship diagram, or ERD for short.

2.1 The building blocks of an ERD

An ERD consists of three kinds of building blocks.

Entities

An entity is a “thing” (object, person, event, …) about which you want to record data. Examples: Customer, Product, Order. Names are given in the singular, because strictly speaking an entity describes a type of object whose data you want to record. This is a widely used convention, but not a universal requirement. A specific customer, such as Johnson, is an instance of that type.

Attributes

Attributes are the properties of an entity, such as the name and city of a customer, the price of a product, or the date of an order. Each instance has one value for each attribute.

One attribute, or a combination of attributes, plays a special role: the key (identifier). The value of the key is different for every instance, so that you can point to each instance unambiguously. For a customer this is the customer number; for a product, the product number.

Relationships

Relationships describe how entities are connected to each other, such as a customer places an order.

2.2 Step 1: finding entities

A design almost always starts with a description of the real world: a conversation with the client, a process description, or a case study. Read the following description of the online store.

Online store description

The online store sells coffee and coffee accessories. For each customer, a customer number, name, and city are recorded. For each product, a product number, a description, and the current price are recorded. A customer can place orders. Each order gets a unique order number and a date. An order can contain several products, each with its own quantity.

A practical approach is to mark the nouns in the text. These are the candidate entities. Then for each word, ask yourself: do we want to record several pieces of data about this, and are there multiple instances of it?

  • Customer: yes, there are many customers, and for each customer you record a number, name, and city. Entity.
  • Product: yes. Entity.
  • Order: yes, with an order number and date. Entity.
  • Name, city, price, date: these are properties of something else. Attributes.
  • Online store: there is only one online store, and that is the organization itself. Not an entity.

Whether something is an entity or an attribute depends on what the organization wants to record. Here, City is an attribute of Customer. If the store also wants to keep track of shipping costs and the delivery service for each city, then City becomes an entity in its own right, with its own attributes.

Rule of thumb: if it has properties of its own that you want to store, it is an entity.

2.3 Step 2: determining attributes and keys

For each entity, write down its attributes. Keep three rules in mind.

  1. Each attribute holds a single value. A customer with two phone numbers does not get one attribute PhoneNumbers containing a list. If it’s a small, fixed number, use separate attributes (HomePhone, MobilePhone). If there can be any number of them, make it an entity of its own.
  2. Don’t store derived data. The total amount of an order can always be calculated from the quantities and prices. If you also store it as an attribute, you run the risk that the two values no longer match.
  3. Choose a key that is unique and stable. A name is not a good key: there are several customers named Johnson. An email address is unique, but it can change. A number the organization assigns itself, such as a customer number, is usually the best choice.
NoteNatural and surrogate keys

A natural key already exists in the real world, such as an ISBN for a book or a license plate number for a car. An artificial key (also called a surrogate key) is created especially for the database, for example an auto-incrementing sequence number. In practice, surrogate keys are often more convenient because they never change.

2.4 Step 3: determining relationships

You find relationships in the verbs of the description: a customer places an order, products appear in an order.

A relationship between two entities has a cardinality: how many instances of one entity belong to how many instances of the other.

2.4.1 Cardinality

Cardinality consists of two parts: a minimum and a maximum.

Maximum cardinality

How many instances can belong at most to one instance on the other side: one or many?

Minimum cardinality

How many instances must belong at least to one instance on the other side: zero (the relationship is optional) or one (the relationship is mandatory)?

You determine the cardinality by asking four questions for each relationship, two in each direction. For the relationship between Customer and Order:

The four questions for the relationship between Customer and Order
Question Answer
Maximum question: How many orders can one customer place at most? Many
Minimum question: How many orders must one customer have placed at least? Zero: a new customer hasn’t ordered anything yet
Maximum question: How many customers can one order belong to at most? One
Minimum question: How many customers must one order belong to at least? One: an order without a customer doesn’t exist

If you look only at the maximums, you can distinguish three kinds of relationships.

One-to-one (1:1)

One instance on one side belongs to at most one instance on the other side. Example: an order leads to at most one invoice, and an invoice belongs to exactly one order.

This is relatively rare and usually indicates that the two tables could be merged, unless there is a specific reason to keep them separate (security, performance, optional data).

One-to-many (1:N)

One instance on one side can belong to many instances on the other side, but not the other way around. Example: a customer places many orders, an order belongs to one customer.

This is by far the most common type.

Many-to-many (N:M)

Many instances can be related in both directions. Example: an order contains many products, and a product appears in many orders.

Relational databases cannot represent this directly. You need a junction table that sits between the two entities.

2.5 Drawing an ERD

In an ERD, you draw an entity as a rectangle. A relationship is shown as a line between two entities.

Special symbols at both ends of the line are used to show the cardinality. There are several ways to do this. For example, you can show a one-to-many (1:N) relationship like this:

┌──────────┐       ┌─────────┐
│ Customer │1─────N│  Order  │
└──────────┘       └─────────┘

2.5.1 Crow’s foot notation

This book uses crow’s foot notation, because it is the most widely used in practice and because it shows both the minimum and the maximum at a glance.

The crow’s foot itself is the symbol for many. When typing on a keyboard, you can use the braces } or {. Dedicated ERD drawing software uses (or something similar).

Each end of a relationship line has two symbols:

  • the symbol closest to the entity indicates the maximum: a bar means 1, a crow’s foot means many.
  • the symbol closest to the line indicates the minimum: a circle means 0 (optional), a bar means 1 (mandatory).
erDiagram
  direction LR
    Customer ||--o{ Order : places
Figure 2.1: One-to-many relationship between Customer and Order
ImportantReading a relationship

You read a relationship in two directions, each time starting from a single instance. Take the relationship between Customer and Order (Figure 2.1).

  • From left to right: One customer places zero or more orders. (Read the symbols next to Order.)
  • From right to left: One order is placed by exactly one customer. (Read the symbols next to Customer.)
The four combinations of symbols in crow’s foot notation
Symbols at the entity Meaning Symbols (left / right)
bar, bar exactly one || / ||
circle, bar zero or one |o / o|
bar, crow’s foot one or more }| / |{
circle, crow’s foot zero or more }o / o{

Figure 2.2 shows the four combinations. Each time, look at the end on the right-hand side.

erDiagram
    A ||--|| B : "exactly one B"
    A ||--o| C : "zero or one C"
    A ||--|{ D : "one or more D"
    A ||--o{ E : "zero or more E"
Figure 2.2: The four combinations of symbols. The end at the right-hand entity shows how many of its instances belong to one A.
NoteAccess notation

Microsoft Access uses its own notation in the Relationships window: 1 and ∞, without a minimum.

TipERD software

There are various programs you can use to draw ERDs. Here are a few free tools that work well.

draw.io: Available both online and as a desktop program, and well suited for drawing ERDs. You can place tables, attributes, keys, and relationships yourself. The program can also be used for other kinds of diagrams.

ERDPlus: Available online only, aimed at learning ER diagrams and database schemas. This makes it a focused learning environment for beginners.

Mermaid: A JavaScript-based diagramming and charting tool that uses text and Markdown-inspired syntax to create and dynamically modify various kinds of diagrams, instead of a traditional drawing program. In short: you write code/text, and Mermaid draws the diagram for you. The ERDs in this book were made with it.

2.6 Step 4: resolving many-to-many relationships

You cannot store a many-to-many relationship directly in a relational database. Try it: where would you record which products are in order 1001? A ProductID column in the order table can hold only one product, and an OrderID column in the product table can hold only one order.

The solution is an associative entity (junction table), a new entity that sits between the two original entities. The many-to-many relationship is thus replaced by two one-to-many relationships (Figure 2.3). In the online store, the associative entity is called OrderLine: each line on an order is one product in a certain quantity.

erDiagram
  direction LR
    Order ||--|{ OrderLine : contains
    Product ||--o{ OrderLine : "appears in"
Figure 2.3: The many-to-many relationship resolved with the associative entity OrderLine

Notice how the cardinalities have carried over. An order contains one or more order lines, a product appears in zero or more order lines, and each order line belongs to exactly one order and exactly one product. Both “many” sides are now next to the associative entity.

An associative entity is often the place for data that belongs to the combination rather than to either entity on its own. Quantity is an example: it is not a property of the order, nor of the product, but of this product in this order.

ImportantPrice at the time of ordering

The current price is an attribute of Product. But if the price goes up next year, the amount of all past orders would change with it, and that is not what we want. If the store wants to know what the customer paid at the time, a second attribute SalePrice belongs in OrderLine. This is not duplicate storage: these are two different facts, the price now and the price then.

2.7 Step 5: from ERD to tables

Once the ERD is finished, you convert it into tables. This follows a fixed set of rules. We write the tables as a relational schema: the name of the table with its columns in parentheses. The primary key is underlined; a foreign key is in italics.

Rule 1: each entity becomes a table. The attributes become columns and the key becomes the primary key.

Rule 2: in a one-to-many relationship, you include the primary key of the one side as a foreign key on the many side. So the order table gets a CustomerID column. The reverse doesn’t work: a customer has several orders, and a column can hold only one order number.

Rule 3: an associative entity gets the primary keys of both entities as foreign keys. Together, they form the primary key of the junction table: the same combination of order and product may appear only once. You can also give the junction table its own surrogate key, such as a line number.

Rule 4: in a one-to-one relationship, you place the foreign key on the optional side. The invoice table gets an OrderID column, not the other way around: not every order has an invoice, so otherwise that column would often be empty. If both sides are mandatory, consider whether the two entities would be better off as a single table.

Rule 5: the minimum determines whether the foreign key may be empty. If the relationship is mandatory on the one side (an order belongs to exactly one customer), the foreign key must always be filled in. If it is optional (zero or one), it may be left empty.

Applied to the online store, this produces the following schema:

  • Customer(CustomerID, Name, City)
  • Product(ProductID, Description, Price)
  • Order(OrderID, OrderDate, CustomerID)
  • OrderLine(OrderID, ProductID, Quantity, SalePrice)

You can also include the attributes and keys directly in the ERD (Figure 2.4). Here, the primary key is marked with PK and a foreign key with FK.

erDiagram
    Customer ||--o{ Order : places
    Order ||--|{ OrderLine : contains
    Product ||--o{ OrderLine : "appears in"
    Customer {
        string CustomerID PK
        string Name
        string City
    }
    Order {
        int OrderID PK
        date OrderDate
        string CustomerID FK
    }
    OrderLine {
        int OrderID PK, FK
        string ProductID PK, FK
        int Quantity
        decimal SalePrice
    }
    Product {
        string ProductID PK
        string Description
        decimal Price
    }
Figure 2.4: The complete ERD of the online store with attributes and keys

2.8 The step-by-step plan in summary

  1. Entities: mark the nouns and determine which of them are entities.
  2. Attributes and keys: give each entity its attributes and choose a unique, stable key.
  3. Relationships: mark the verbs and, using the four questions, determine the minimum and maximum on both sides of each relationship.
  4. Resolve many-to-many: replace each many-to-many relationship with an associative entity and two one-to-many relationships.
  5. Convert to tables: apply the conversion rules and write down the relational schema.

Finally, check the result against the sample data: can you record every situation from the description without storing data twice? In Chapter 3 you will learn a systematic way to test this.

2.9 Common mistakes

  • Treating attributes as entities. For example, creating a separate table for Address, which is usually an attribute of a person or a company, unless you want to keep an address history.
  • Forgetting junction tables for N:M relationships.
  • Placing the foreign key on the wrong side of a one-to-many relationship.
  • Including a report or overview as an entity, such as SalesReport. That is output you calculate from the data.
  • Storing derived attributes, such as TotalAmount or Age (store the date of birth instead).

2.10 Exercises

Exercise 1: reading relationships

Describe each of the three relationships in Figure 2.4 in two sentences, one for each reading direction.

- One customer places zero or more orders. One order is placed by exactly one customer.

- One order contains one or more order lines. One order line belongs to exactly one order.

- One product appears in zero or more order lines. One order line concerns exactly one product.

Exercise 2: determining cardinality

For each of the following relationships at a college, determine the minimum and maximum on both sides. Then draw an ERD.

  1. Student and student ID card: each student gets one card; a card belongs to one student. A new student doesn’t have a card yet.
  2. Department and employee: each employee works in exactly one department; a department has at least one employee.
  3. Instructor and course: each course has exactly one coordinating instructor; an instructor coordinates zero or more courses.
  4. Student and course: a student enrolls in zero or more courses; zero or more students enroll in a course.
  1. One-to-one. A student has zero or one card; a card belongs to exactly one student.

erDiagram
    direction LR
    Student ||--o| StudentCard : has

  1. One-to-many. A department has one or more employees; an employee belongs to exactly one department.

erDiagram
    direction LR
    Department ||--|{ Employee : employs

  1. One-to-many. An instructor coordinates zero or more courses; a course has exactly one coordinator.

erDiagram
    direction LR
    Instructor ||--o{ Course : coordinates

  1. Many-to-many.

erDiagram
    direction LR
    Student }o--o{ Course : "enrolls in"

You can resolve this many-to-many relationship with an associative entity Enrollment.

erDiagram
    direction LR
    Student ||--o{ Enrollment : makes
    Course ||--o{ Enrollment : has

Exercise 3: bike rental

Read the following description and create an ERD and a relational schema.

A bike rental company rents bikes to customers. For each customer, a customer number, name, and phone number are recorded. Each bike has a unique bike number, a type (city bike, e-bike, tandem), and a daily rate. For each rental, a rental number, a start date, and an end date are recorded. A customer can rent several bikes at once in a single rental, for example for the whole family. Naturally, a bike is rented out many times over time.

Entities: Customer, Bike, and Rental. There is a many-to-many relationship between Rental and Bike, which is resolved with the associative entity RentalLine.

erDiagram
    Customer ||--o{ Rental : makes
    Rental ||--|{ RentalLine : includes
    Bike ||--o{ RentalLine : "is rented in"
    Customer {
        int CustomerID PK
        string Name
        string Phone
    }
    Rental {
        int RentalID PK
        date StartDate
        date EndDate
        int CustomerID FK
    }
    RentalLine {
        int RentalID PK, FK
        int BikeID PK, FK
        decimal DailyRate
    }
    Bike {
        int BikeID PK
        string Type
        decimal DailyRate
    }

Relational schema:

  • Customer(CustomerID, Name, Phone)
  • Bike(BikeID, Type, DailyRate)
  • Rental(RentalID, StartDate, EndDate, CustomerID)
  • RentalLine(RentalID, BikeID, DailyRate)

The daily rate appears twice in the schema, just like the price in the online store: in Bike it is the current rate, in RentalLine the rate that applied to this rental. If you assume the rate never changes, you can leave the attribute out of RentalLine.

A good alternative is a separate entity BikeType holding the daily rate, if all bikes of the same type have the same rate.

Exercise 4: what’s wrong?

A student created the following ERD for an online store. Name at least four mistakes and explain how to fix them.

erDiagram
  direction LR
    Customers ||--o{ Order : places
    Order }o--|{ Product : contains
    Customers {
        string Name PK
        string PhoneNumbers
    }
    Order {
        int OrderID PK
        date OrderDate
        decimal TotalAmount
    }
    Product {
        string ProductID PK
        string Description
        decimal Price
        int OrderID FK
    }

  1. Customers is plural; it should be Customer.

  2. Name is not a suitable key, because names are not unique. Add a customer number as the key.

  3. PhoneNumbers holds multiple values in one attribute. Use a single attribute Phone, a fixed number of separate attributes, or an entity of its own.

  4. TotalAmount is derived from quantities and prices and should not be stored.

  5. Order has no foreign key CustomerID, even though the relationship with Customer requires one.

  6. The many-to-many relationship between Order and Product has not been resolved. The foreign key OrderID in Product is an attempt at this, but it means each product can appear in only one order. Add the associative entity OrderLine with OrderID, ProductID, and Quantity, and remove OrderID from Product.