How do you design a self-referencing table, and what are its uses?

Instruction: Describe how to create a self-referencing table and provide examples of its applications.

Context: This question assesses the candidate's knowledge in database schema design, specifically with self-referencing tables, and their ability to understand hierarchical or recursive data relationships.

Updated

Official answer

A self-referencing foreign key relates rows within one table. For a PostgreSQL organization where each employee has at most one direct manager:

CREATE TABLE employees (
  employee_id integer PRIMARY KEY,
  name text NOT NULL,
  manager_id integer REFERENCES employees(employee_id),
  CHECK (manager_id IS DISTINCT FROM employee_id)
);

A NULL manager identifies a root. The foreign key rejects nonexistent managers, and the check rejects direct self-management. Neither prevents a longer cycle such as A reporting to B and B to A; enforce that invariant with an appropriate transaction-safe application/database design and validate existing data.

A self-join fetches direct managers; a recursive query traverses a chain or subtree with cycle protection. This model makes parent changes simple but traversal cost depends on depth, size and indexes.

Many-to-many relationships, such as users following multiple other users, need an edge table with two foreign keys to users and a unique pair. A single manager-like column cannot represent all those relationships.

Related Questions