What Are Relationships?

A column that points at another table

A relationship is a column in one table whose values are drawn from the identifying column of another table. In an issue tracker every issue belongs to a project, so the issues table has a project_id column, and each value in it matches the project_id of exactly 1 row in projects. The issue row stores no project name and no project owner. It stores a reference, and the reference is the relationship.

Read issue 103 and you learn it belongs to project 2. Which project that is lives in the other table, and a join is how the database follows the reference back to it. The join condition names both ends of the relationship: the reference column on one side and the identifying column on the other.

SELECT
  i.issue_id,
  i.title,
  p.name AS project_name
FROM issues i
JOIN projects p
  ON i.project_id = p.project_id
WHERE i.status = 'open'

Why not one wide table?

Copy the project name onto every issue row instead and the 3 issues carry 'Billing', 'Billing' and 'Mobile App'. Renaming the Billing project to Payments now means updating 2 issue rows, and a 4th issue inserted with 'billing' in lowercase quietly creates a 3rd project that nobody intended. With a reference column, the name lives in 1 row of projects: a rename touches that row alone, and a typo in project_id fails at insert time because no project 7 exists.

Row 104 is the failure in miniature. A report grouping by project_name now shows 3 projects, the Billing total is short by 1 issue, and the only clue is a lowercase letter. The wide table has no column whose value could be rejected, so the mistake is accepted and stored.

Project name copied onto each issue

• A rename touches every issue in the project
• A misspelling creates a phantom project
• No join needed to read the name
• Nothing stops an issue naming a project that was never created

Issue holds project_id

• A rename touches 1 row
• An unknown project_id is rejected
• Reading the name costs 1 join
• Every issue points at a project that exists

PK and FK badges on a schema diagram

A schema diagram draws each table as a card. The column that identifies a row carries a PK badge, short for primary key, and a column holding another table's identifier carries an FK badge, for foreign key. The line between those 2 badges is the relationship. Everything that follows in this lesson is a question about that line: how many rows sit at each end, whether an end may be empty, and whether both ends can be the same table.

One sentence per table

A relationship also fixes what one row of each table means. One row of projects is one project. One row of issues is one issue inside exactly one project. Say that sentence for every table in a schema and the relationships fall out of it: the phrase 'inside exactly one project' is the project_id column. When the sentence for a table needs the word 'several', the reference can't live on that table, which is the subject of the next section.

An orders table has a customer_id column and a customers table has a customer_id column that identifies each customer. Which statement describes the relationship?

• orders.customer_id references customers.customer_id, so each order row points at one customer row.
• customers.customer_id references orders.customer_id, so each customer row points at one order row.
• The 2 columns are unrelated until a query joins them, because tables cannot refer to each other.
• orders.customer_id references customers.customer_id, so the customer's name is copied into each order row.

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.