DBMS · Design and models

ER diagram and relationships

Easyer diagramrelationshipsdesign

Interview question

What is an ER diagram, and what are one-to-one, one-to-many and many-to-many relationships?

How to answer

The short answer

An ER diagram, short for entity-relationship diagram, is a picture of the data you plan to store. It shows the entities (things such as Customer or Order), their attributes (details such as name or amount), and the relationships between them. Designers draw it before creating tables.

There are three kinds of relationship.

One-to-one: each row on one side matches at most one row on the other. A user and their profile settings are an example. It is usually modelled with the key of one table also being the key of the other.

One-to-many: one row on one side matches many rows on the other. One customer places many orders, but each order belongs to one customer. It is modelled by putting a on the "many" side, here orders.customer_id.

Many-to-many: many rows on each side match many on the other. A student takes many courses, and a course has many students. A cannot hold this directly, so you add a junction table (also called a bridge table) with a foreign key to each side:

SQL
CREATE TABLE enrolments (
    student_id INT REFERENCES students(student_id),
    course_id  INT REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id)
);

Turning an ER diagram into tables

  1. 1Each entity becomes a table, and each attribute becomes a column.
  2. 2Pick a for every table.
  3. 3Add a foreign key for each one-to-many link.
  4. 4Add a junction table for each many-to-many link.

Interview shortcut

> One-to-many puts the foreign key on the many side. Many-to-many needs a third table.