Search⌘ K
AI Features
Home
Courses
SQL Antipatterns: Avoiding the Pitfalls of Database Programming

Synopsis: Naive Trees

Explore how naive tree structures represent hierarchical data in SQL using adjacency lists. Understand the challenges of querying deep recursive relationships like threaded comments and discover when adjacency lists are appropriate and their limitations.

We'll cover the following...

Let’s suppose you work as a software developer for a famous website for science and technology news.

It’s a modern website, so readers can write comments and reply to each other, forming threads of discussion that both branch out and extend deeply. You want to track these reply chains and choose a simple solution for it—that each comment references the comment to which it replies.

MySQL
CREATE TABLE Comments (
  comment_id SERIAL PRIMARY KEY,
  parent_id BIGINT UNSIGNED,
  comment TEXT NOT NULL,
  FOREIGN KEY (parent_id) REFERENCES Comments(comment_id)
);

It soon becomes clear, however, that it’s hard to retrieve a long chain of replies in a single SQL query. You can only get the immediate children or, at best, the grandchildren, up to a fixed depth. But the threads can have an ...