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 parent_id, nleft, nright and 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).

• Next: Using The Nested Tree Data