SQL Antipatterns, Volume 1

Antipattern: Always Depend on One’s Parent

The naive solution commonly shown in books and articles is to add a column parent_id. This column references another comment in the same table, and you can create a foreign key constraint to enforce this relationship.

Trees/anti/adjacency-list.sql

​CREATE​ ​TABLE​ Comments (
  comment_id   ​SERIAL​ ​PRIMARY​ ​KEY​,
  bug_id       ​BIGINT​ ​UNSIGNED​ ​NOT​ ​NULL​,
  author       ​BIGINT​ ​UNSIGNED​ ​NOT​ ​NULL​,
  comment_date ​DATETIME​ ​NOT​ ​NULL​ ​DEFAULT​ ​CURRENT_TIMESTAMP​,
  ​comment​      ​TEXT​ ​NOT​ ​NULL​,
  parent_id    ​BIGINT​ ​UNSIGNED​,
  ​FOREIGN​ ​KEY​ (parent_id) ​REFERENCES ...