Relationships and cardinality

01The problem

At Harbourline, a shipment is usually paid in one go, but some customers pay in two instalments. If the model assumed "one payment per shipment", a payment_date column on shipments would have nowhere to put the second payment. Getting how many right, for each relationship, decides where every column goes.

02The concept

Cardinality is how many rows on one side can relate to a row on the other.

Optional or mandatory. Each end also says whether a related row must exist:

• A shipment must have exactly one customer (mandatory, one).
• A customer may have zero shipments, if they've just signed up (optional, many).

In crow's-foot notation, a bar means "one", a crow's foot means "many", and a circle means "zero is allowed". The legend in the diagram shows all four endings.

Why many-to-many needs a bridge. You can't put course_id on the students table (a student takes several courses) or student_id on courses (a course has several students). So you create a table with one row per pairing, enrolments(student_id, course_id, enrolled_on), turning one many-to-many into two one-to-manys. The bridge often carries its own facts, such as the enrolment date or a grade.

03Example

Harbourline's shipments-to-payments relationship is one-to-many, and optional on the payments side. How many payments do shipments have?

SELECT payments_per_shipment, COUNT(*) AS shipments
FROM (
  SELECT s.shipment_id, COUNT(p.payment_id) AS payments_per_shipment
  FROM shipments AS s
  LEFT JOIN payments AS p ON p.shipment_id = s.shipment_id
  GROUP BY s.shipment_id
)
GROUP BY payments_per_shipment
ORDER BY payments_per_shipment;

Some shipments have 0 payments (not yet paid, or cancelled), most have 1, and some have 2. The model has to allow all three, which it does because payments is its own table.

04Walkthrough

For each pair of entities, ask two questions in both directions:

1. Can one A have many Bs? and Can one B have many As?
   • Yes / No → one-to-many (FK on B).
   • Yes / Yes → many-to-many (bridge table).
   • No / No → one-to-one.
2. Must every A have a B? (mandatory or optional at each end)

Ashgrove Chambers, the law firm: can a client have many matters? Yes. Can a matter have many clients? In this data, no, so it's one-to-many and matters.client_id is the foreign key. (If the firm often acted for several clients jointly on one matter, it would need a matter_clients bridge.)

05Practice

06Check your understanding