Adjacency List

The smell occurs when an attribute in a table refers another row in the same table i.e., a table has a recursive relationship to model hierarchical structure.

Querying a tree with adjacency list is quite difficult and error-prone. Specifically, deleting a node from a tree which is modelled using adjacency list is non-trivial and prone to introduce errors in the database.

Example

CREATE TABLE Comments (
comment_id SERIAL PRIMARY KEY,
parent_id BIGINT UNSIGNED,
-- , , ,
FOREIGN KEY (parent_id) REFERENCES Comments (comment_id),
--, , ,
};

Tools

The following set of tools detects this smell: DbDeo(for SQL)

Reference

B. Karwin, “SQL Antipatterns: Avoiding the Pitfalls of Database Programming”. Pragmatic Bookshelf, 2010.