Entities, Relationships & Cardinality

A data model is a plan for how data is stored. In chapter 1 you saw why a plan helps. This chapter gives you three words that every plan uses.

• Entity: a kind of thing the business keeps data about, such as a customer.
• Relationship: a link between two entities, such as a customer places an order.
• Cardinality: how many rows on one side can link to one row on the other side.

A picture of entities and relationships is an entity-relationship diagram, or ERD. Step through the three moves below to see one grow.

The shop data

Most examples in this chapter use the shop from the SQL chapters. Here are its tables.

One change from the SQL chapters: each product now has its own row in products. The table order_items stores the product_id, not the product name.

Two facts matter most in this chapter.

• Omar is a customer, but he has no orders.
• Order 105 exists, but it has no items.

An entity is a kind of thing the business keeps data about. In the shop, customer, order and product are entities.

An attribute is one fact about an entity. A customer has a name and a country.

Look at the customers table above. The table is the entity. Each row is one customer, and each column is an attribute.

Asha is one row. Her name and country are two attributes.

The value customer_id picks out exactly one customer, so it is the identifier. In the SQL chapters you met it as the primary key.

In a diagram, each attribute is one line in the entity’s box.

Is it an entity or an attribute?

This is the first real modeling choice. A word can be either one, and you decide by what you want to store.

Make five choices about an order. The widget builds the model from your answers.

A relationship is a link between two entities. Name it with a verb. In the shop, a customer places an order.

Every relationship reads two ways. Customer to order: Asha places orders 101 and 102. Order to customer: order 101 is placed by Asha.

In an interview, say the relationship both ways before you draw. Then give its cardinality, which the next section explains.

Check both readings against the orders table. Customer 1 has two orders, and each order names one customer.

SELECT order_id, customer_id
FROM orders
WHERE customer_id = 1;

In the database, a relationship becomes a foreign key. A foreign key is a column that holds the key of a row in another table. Here it is orders.customer_id.

Flip the reading in this widget. It shows why the foreign key sits on the orders table.

Cardinality counts the links. Pick one row on one side. How many rows on the other side can it link to?

Then ask the same question from the other side.

Take customers and orders. Customer to orders: many. Order to customer: one.

Many and one together is one-to-many.

The two answers give one of three shapes.

UNIQUE means no value can repeat, so each customer has at most one card. You may also hear many-to-one. It is the one-to-many shape, read from the other end.

Count the orders of each customer. This query uses GROUP BY from a later SQL chapter, so only read the result.

SELECT c.name, COUNT(o.order_id) AS orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

Now decide four pairings yourself. Ask "could there ever be two?" in both directions. This widget uses other examples, not the shop.

An ERD draws cardinality as small marks at the ends of a line. This style is called crow’s-foot notation. Here is the link between customers and orders.

CUSTOMER  ||——o<  ORDER

There are three marks to learn.

• A bar ( | ) means one.
• A fork ( < or > ), like a bird’s foot, means many.
• A ring ( o ) means zero.

Read each end by the box it touches. The marks next to ORDER say how many orders one customer has.

Next to the box is the maximum: | is one, and < is many. The mark inside it is the minimum: o is zero, and | is one.

So ||——o< means: each customer has zero or many orders. Each order has exactly one customer. Omar has zero orders, so the ring fits.

Set the marks in this widget. It starts with the shop link above.

Try two changes. Set the marks next to CUSTOMER to minimum zero: an order may now have no customer. Then set the maximum to many: both ends are many, so the widget says you need a bridge table.

Cardinality and optionality are two separate choices. Cardinality says how many. Optionality says whether a link is optional or required.

In a diagram, optionality is the inner mark: the ring or the bar.

In the database, a required link is a NOT NULL foreign key. NOT NULL means the column cannot be empty. An optional link can be empty.

Compare two foreign keys on one table.

CREATE TABLE orders (
  order_id    BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers (customer_id),
  promo_id    BIGINT REFERENCES promotions (promo_id)
);

Choose the business rule and the column in this widget. It then shows two new orders going into the orders table.

Two of the four combinations are correct. Of the two wrong ones, one fails with an error. The other lets bad rows in and shows no error.

Look at order_items with the product names added.

Order 101 has two products: a Pen and a Notebook.

The Pen is in two orders: 101 and 104. This is many-to-many.

Neither table can hold this link in one column. A foreign key holds one value, and a list of ids in one column cannot be joined or checked.

The fix is a bridge table. It stores one row for each pair. In the shop, order_items is the bridge between orders and products.

CREATE TABLE order_items (
  order_id   BIGINT  NOT NULL REFERENCES orders (order_id),
  product_id BIGINT  NOT NULL REFERENCES products (product_id),
  quantity   INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Now use the bridge. Which orders contain a Pen?

SELECT i.order_id, i.quantity
FROM order_items AS i
JOIN products AS p ON p.product_id = i.product_id
WHERE p.name = 'Pen';

Now try the steps on a small case from the shop. The last step asks where quantity belongs.

Take orders joined to customers. Each order has exactly one customer, so six orders stay six rows.

SELECT COUNT(*) AS rows_returned
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id;

Now take orders joined to order_items. Order 101 has two items, so it appears twice. Look at the amount column.

SELECT o.order_id, o.amount, i.product_id
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id;

This repeat is called fan-out. The real total of the six orders is 220.50. The seven rows count order 101 twice (40.00) and order 106 twice (80.00).

So 220.50 + 40.00 + 80.00 = 340.50. A sum over the repeated rows gives the wrong total.

The rule: joining from the one side to the many side repeats each one-side row. Joining from the many side to the one side adds no rows.

A LEFT JOIN from customers to orders follows the same rule. Omar has no orders, but he keeps one row, with NULL for the order.

SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id;

Some rows cannot be told apart without their parent. Take order lines. Every order has a line 1, so line_no alone is not unique.

The key must include the parent key: (order_id, line_no). An entity like this is a weak entity. It borrows part of its key from its parent.

The order_items bridge is a similar case. Its key is (order_id, product_id). Chapter 3 covers keys in depth.

Now change the primary key in this widget. It uses order lines for orders 101 and 102. They are not a shop table.

Requirements arrive as sentences. You can read them like grammar. Each kind of word maps to one part of the model.

Try it on these sentences: "A customer places many orders. Each order contains many products. A product can be in many orders."

Pick a tag, then click the words it fits. Small joining words carry no model.

Look at order and product. An order has many products, and a product is in many orders. Many on both ends means a bridge table: order_items.

Each exercise opens an ERD canvas with its own data. You draw the model, and a review checks it against real rows.

Start with the first one. The last three use keys from chapter 3, so they are optional until then.