Implementing An N-Level Nested Tree In PHP And PostgreSQL, Part 1
Tree SQL Schema
create table nested_tree ( id serial not null, parent_id int not null, title varchar(255) not null, nleft int not null, nright int not null, nlevel int not null, primary key (id) ); create index nested_tree_parent_id on nested_tree (parent_id); create index nested_tree_nleft on nested_tree (nleft); create index nested_tree_nright on nested_tree (nright); create index nested_tree_nlevel on nested_tree (nlevel);
We create indexes on the
nlevel as these are the columns that are most often queried.
For nodes that don’t have a parent, we give them a
parent_id of 0. We could instead use
null, and then enforce a foreign key constraint on the
parent_id, but I find it just makes lookups and rebuidling the tree more complicated.
To make this schema work in MySQL, the
id column should have a description of
id int not null auto_increment.
You can store any amount of data in the table in addition to these fields. For example, (at time of writing) the table for PhpRiot also has a column which stores the article data, and a column used to determine the order of articles within a section (this is used when the tree is rebuilt for ordering a tree with uncalculated nleft/nright values – this will be dealt with in part 2 of this series).