Self-Referential Tables

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.

Joining a table to itself

SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m
  ON e.manager_id = m.employee_id

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.

How deep does it go?

Going 2 levels up takes a 2nd alias and a 2nd join, and 3 levels takes a 3rd. Each join is a fixed step, so a query written with 3 joins reaches great-grandparents and never sees a 4th level. Eli's skip-level manager is 2 joins away.

SELECT
  e.name AS employee,
  m2.name AS skip_level
FROM employees e
JOIN employees m1
  ON e.manager_id = m1.employee_id
JOIN employees m2
  ON m1.manager_id = m2.employee_id
WHERE e.employee_id = 4

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.

Self-referential many-to-many

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.

SELECT
  u.name,
  COUNT(f.follower_id) AS follower_count
FROM users u
LEFT JOIN follows f
  ON f.followed_id = u.user_id
GROUP BY u.user_id, u.name
ORDER BY follower_count DESC

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 on an issue can be replies to other comments, forming threads of any depth. A top-level comment replies to nothing. How should the comments table hold this?

• Add a parent_comment_id column referencing comments.comment_id, with NULL rejected.
• Add a replies table holding a copy of each reply's body and the comment_id it answers.
• Add a parent_comment_id column referencing issues.issue_id, NULL for top-level comments.
• Add a parent_comment_id column referencing comments.comment_id, NULL for top-level comments.

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.