Cardinality Explained

Cardinality is the answer to 2 questions asked about tables A and B: can one row of A relate to many rows of B, and can one row of B relate to many rows of A? The answers decide where the reference column lives, and in one case they decide that no single column can hold it.

One-to-many: the reference sits on the many side

A project has many issues and an issue has one project. Try putting the reference on the project: projects.issue_id holds 1 value, so a project with 3 issues needs 3 columns, and a 4th issue needs a schema change. Put it on the issue and the problem disappears, because every issue has exactly 1 project to name. The reference column always lives on the many side, where each row has a single thing to point at. The Billing project in the previous section is 1 row; its 2 issues each carry project_id = 1.

One-to-one: pick a side and declare it unique

A user has 1 settings row and a settings row belongs to 1 user. Either table could hold the reference, so put it on the table that is optional or arrived later: user_settings.user_id points at users, and users is untouched. The split is usually made to keep a narrow table that every request reads apart from wide columns that almost nothing reads. The database only knows the relationship is 1:1 if user_settings.user_id is declared unique. Without that declaration it is a 1:N that happens to have 1 row per user today, and nothing stops a 2nd settings row from appearing tomorrow.

Many-to-many needs a third table

An issue can carry many labels and a label sits on many issues. Neither side has a single thing to point at, so neither table can hold the reference column. The tempting shortcut is a text cell on issues holding '3,7,12'. That cell cannot be joined and can't be indexed, so 'which issues carry label 7' becomes a full scan with string matching, and '7' also matches '17'. The structure that works is a 3rd table whose rows are the pairs.

One row of issue_labels means 'this issue carries this label'. The pair of columns together identifies the row, so issue 101 cannot carry label 7 twice. The table is often called a junction table, and it's a real table: it can hold columns of its own, such as who applied the label and when, that have nowhere else to live.

SELECT
  l.name,
  COUNT(*) AS issue_count
FROM issue_labels il
JOIN labels l
  ON il.label_id = l.label_id
GROUP BY l.name
ORDER BY issue_count DESC

The query never touches issues. The junction table already holds one row per issue and label pair, so counting its rows per label is the answer, and the join to labels only fetches the name. A many-to-many relationship makes 2 directions of question cheap at once, given an index for each: the pair key, led by issue_id, serves labels for an issue, and a second index on label_id serves issues for a label.

Today's data can mislead. A customer with 1 address this year is still a 1:N if the business allows a 2nd address, and a schema built on the 1:1 assumption breaks the day the 2nd address arrives.

A university schema has students and courses. A student enrolls in many courses and a course has many students. A teammate proposes adding a course_ids text column to students holding values like '201,305,410'. What should the model hold instead?

• A course_id column on students, declared unique, so each student is tied to one course.
• A student_id column on courses, so each course points at the student taking it.
• An enrollments table with student_id and course_id, one row per student and course pair.
• An enrollments table with student_id and a course_ids text list, one row per student.

About this lesson

This section is part of the Relationships lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.