Required vs Optional

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.

The join changes with the minimum

The minimum is a fact about the data, and it decides which join is correct. Here is the issue list with the assignee's name attached.

SELECT
  i.issue_id,
  i.title,
  u.name AS assignee
FROM issues i
LEFT JOIN users u
  ON i.assignee_id = u.user_id

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.

Minimums on the one side

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.

Deleting the parent row

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.

Required (NULL rejected)

• Inner join is safe
• Parent delete is blocked or cascades to the children
• Child row cannot be inserted before its parent
• Example: issue to project

Optional (NULL allowed)

• Left join or rows go missing
• Parent delete blocks by default, or can be declared to set NULL
• Child row can exist alone and be linked later
• Example: issue to assignee

The decision is cheaper to make at design time than to reverse. Making a required column optional later means every existing query that inner-joins through it is now wrong. Making an optional column required later means finding a value for every NULL already stored, which may be thousands of rows with no right answer.

An employees table has department_id referencing departments. New hires spend their first 2 weeks in onboarding before a department is chosen. Headcount reports must count every employee. Which design is right?

• Keep department_id required and create an 'Onboarding' department that new hires belong to for 2 weeks.
• Make department_id optional, and have headcount reports left join to departments.
• Make department_id optional, and have headcount reports inner join to departments.
• Keep department_id required and insert new hires only after their department is chosen.

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.