Complex Patterns

Polymorphic: one column, several targets

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.

SELECT
  c.comment_id,
  c.body,
  COALESCE(i.title, p.name) AS target_name
FROM comments c
LEFT JOIN issues i
  ON c.issue_id = i.issue_id
LEFT JOIN projects p
  ON c.project_id = p.project_id

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.

Circular: 2 tables that point at each other

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.

A circular pair hides a second rule the schema cannot state: the head of Sales should belong to Sales. Both references can be valid while Chen, an engineer, heads Sales. That check lives in code at write time, and it is worth a comment in the schema so the next reader knows the loop exists.

Do

• Give every reference a single target table the database can check
• Make one side of a circular pair optional so the first rows can be inserted
• Write down rules the schema cannot enforce next to the tables they apply to
• Choose the polymorphic design by how bad an orphaned row would be

Avoid

• Store a list of identifiers in a text cell
• Rely on a type column alone to keep references valid
• Make both halves of a circular pair required
• Assume today's row counts are the business rule

Capstone: design the reactions model

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.