SQL Antipatterns: Avoiding the Pitfalls of Database Programming/

...

/

Maintaining a Tree with an Adjacency List

Maintaining a Tree with an Adjacency List

Let's explore the process of maintaining a tree with an Adjacency List.

We'll cover the following...

Many operations can be accomplished using the Adjacency List.

Inserting a new leaf node

Some operations are simple to accomplish with an Adjacency List, such as adding a new leaf node.

Let’s see how we can add a new leaf node:

MySQL
INSERT INTO Comments (bug_id, parent_id, author, comment)
     VALUES (1234, 7, 'Kukla', 'Thanks!');

After inserting the record, let’s retrieve the data in the next playground.

MySQL
INSERT INTO Comments (bug_id, parent_id, author, comment)
     VALUES (1234, 7, 'Kukla', 'Thanks!');
SELECT comment_id, parent_id, author, comment FROM Comments 
 WHERE bug_id = 1234;

Relocating a single node or a subtree

The process of relocating a single node or a subtree is also easy. Have a look at the next query for more clarification:

MySQL
UPDATE Comments SET parent_id = 3 WHERE comment_id = 6;

We are updating the table by setting 3 as a parent of 6 in the query. Now the parent of node 6 is 3. Let’s ...