Relationships

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.

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.

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.

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.

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.

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.

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.

Cardinality says how many rows can sit at each end of a relationship. The second question is the minimum: must a row exist at the other end at all? Every issue must belong to a project, so issues.project_id is required, the column rejects NULL, and an insert with no project fails. An issue can sit unassigned for days, so issues.assignee_id is optional, and NULL in that column means nobody yet.

Swap LEFT JOIN for JOIN and issue 102 disappears, because an inner join keeps only rows with a match on both sides, and NULL matches nothing. A dashboard that counts open issues through an inner join to users undercounts by every unassigned issue, and no error is raised anywhere. The rule follows from the minimum: a required relationship can be inner-joined safely, and an optional relationship needs a left join or the unmatched rows vanish from the result.

A project can exist with 0 issues. That minimum sits at the other end of the line, and a column constraint can't enforce it, because there is no column on projects that turns NULL when its last issue is deleted or moved away. If the business rule is 'a project must have at least 1 issue', the rule lives in application code or in a check that runs at write time. The schema alone will not hold it, and a reviewer reading the schema can't see it, so write it down where the next engineer will look.

The minimum also decides what a delete does. When user 12 leaves the company, issue 101 can keep existing with assignee_id set back to NULL, because the relationship was optional from the start. When project 1 is deleted, its 2 issues cannot keep existing with a required project_id pointing at nothing. The usual choices are to block the delete until the issues move, or cascade it to the issues. Reassigning them to a default parent, such as an Archived project, is the other declared option.

A reference column can point at the table it lives in. An employee has a manager, and the manager is an employee, so employees.manager_id holds the employee_id of another row in the same table. That is 1 table with 1 relationship, and both ends of the line sit on the same card. Org charts, folder trees and nested comment threads all use this pattern.

Ruth's manager_id is NULL because the CEO reports to nobody. Every tree has at least 1 root, and the root needs a marker for having no parent. A root pointing at itself passes the reference check too, but it plants a loop that every hierarchy query must guard against, so NULL, the optional minimum from the previous section, is the clear marker.

The table appears twice under 2 aliases: e is the row being reported and m is the row it points at. Without the aliases the database cannot tell which employees.name is wanted. The left join keeps Ruth, whose manager_id matches no row.

When the depth is unknown, a fixed number of joins can't answer 'everyone under Chen'. A recursive query can walk the tree at read time. Two stored columns trade write-time maintenance for cheaper reads. A level column stores the depth of each row, 0 for Ruth and 2 for Eli, so 'all employees 2 levels below the CEO' is a filter. A path column stores the chain of identifiers from the root, so Eli's path is /1/2/4/, and 'everyone under Chen' is WHERE path LIKE '/1/2/%' AND employee_id <> 2, since the pattern also matches Chen. The catch is that when Eli moves under Dana, his path and the path of everyone beneath him must be rewritten.

A user follows many users and is followed by many users. Both directions answer yes, so the pairs go in a junction table, and both of its columns reference users. One row of follows means 'this user follows that user'. The 2 columns together identify the row, so nobody can follow the same person twice, and a rule that follower_id differs from followed_id stops a user following themselves.

Swapping followed_id for follower_id in the join turns the same query into 'how many people each user follows'. One junction table answers both directions, which is the point of holding the pairs rather than a list on either side.

Comments were attached to issues. Now the product wants comments on projects too, and the same comments table should serve both. A reference column points at 1 table, so a comment that might belong to an issue or to a project has no single column to hold the reference. Each of the workarounds below gives up a different amount of enforcement to get that flexibility.

The table above is the first design: a target_type column saying which table, and a target_id saying which row. Any new target is a new string value and no schema change. The price is that the database cannot check target_id against anything, because one column cannot reference 2 tables. Comment 503 could name issue 999, which does not exist, and the insert succeeds. Deleting issue 103 leaves comment 503 behind with no error. Every join needs a filter on target_type first.

The second design gives each target its own column: issue_id and project_id, both optional, with a rule that exactly 1 of them is filled. Each column is a real reference, so an unknown issue is rejected and a deleted issue can clear its comments. Adding a 3rd target later means adding a 3rd column and extending the rule. Reading the target's name is 2 left joins and a COALESCE.

The third design splits the table: issue_comments with a required issue_id, and project_comments with a required project_id. Each is a plain one-to-many with the strongest enforcement, but a query that wants everything one user wrote now has to combine both tables with a UNION ALL.

Every employee belongs to a department, and every department has a head who is an employee. That gives employees.department_id referencing departments and departments.head_employee_id referencing employees. Make both required, with each checked immediately, and neither row can be inserted first: the first department needs a head who exists, and the first employee needs a department that exists. A database that defers the check to commit can insert both in one transaction. Without that, make one reference optional: a department is created with head_employee_id NULL, employees are inserted into it, and then the head is set. Or move the head into its own table, department_heads with department_id and employee_id, inserted last.