Nested Sets
Let's see how Nested Sets can solve the Naive Trees antipattern.
We'll cover the following...
The Nested Sets solution stores information with each node that pertains to the set of its descendants rather than the node’s immediate parent. This information can be represented by encoding each node in the tree with two numbers, which we can call nsleft and nsright.
Creating Comments table with nsleft and nsright
Let’s create the Comments table, with nsright and nsleft as its columns.
CREATE TABLE Comments (comment_id SERIAL PRIMARY KEY,nsleft INTEGER NOT NULL,nsright INTEGER NOT NULL,bug_id BIGINT UNSIGNED NOT NULL,author BIGINT UNSIGNED NOT NULL,comment_date DATETIME NOT NULL,comment TEXT NOT NULL,FOREIGN KEY (bug_id) REFERENCES Bugs (bug_id),FOREIGN KEY (author) REFERENCES Accounts(account_id));
Assigning nsleft and nsright
Each node is given nsleft and nsright numbers in the following way: the value of the nsleft number is always less than the value of the numbers of all of the node’s children, whereas the value of the nsright number is greater than that of all of the node’s children. These numbers have no relation to the comment_id values.
An easy way to assign these values is by following a ...