What is the best way to represent a hierarchy in an SQL database? General, portable equipment?
Suppose a hierarchy is mostly readable, but not completely static. Let's say this is a family tree.
Here's how to do it:
create table person ( person_id integer autoincrement primary key, name varchar(255) not null, dob date, mother integer, father integer );
And inserting such data:
person_id name dob mother father 1 Pops 1900/1/1 null null 2 Grandma 1903/2/4 null null 3 Dad 1925/4/2 2 1 4 Uncle Kev 1927/3/3 2 1 5 Cuz Dave 1953/7/8 null 4 6 Billy 1954/8/1 null 3
Instead, split the nodes and your relationships into two tables.
create table person ( person_id integer autoincrement primary key, name varchar(255) not null, dob date ); create table ancestor ( ancestor_id integer, descendant_id integer, distance integer );
Data is created as follows:
person_id name dob 1 Pops 1900/1/1 2 Grandma 1903/2/4 3 Dad 1925/4/2 4 Uncle Kev 1927/3/3 5 Cuz Dave 1953/7/8 6 Billy 1954/8/1 ancestor_id descendant_id distance 1 1 0 2 2 0 3 3 0 4 4 0 5 5 0 6 6 0 1 3 1 2 3 1 1 4 1 2 4 1 1 5 2 2 5 2 4 5 1 1 6 2 2 6 2 3 6 1
Now you can run arbitrary queries that are not related to joining the table to yourself, which will happen if you have a heirachy relation on the same line as node.
Who has grandparents?
select * from person where person_id in (select descendant_id from ancestor where distance=2);
All your descendants:
select * from person where person_id in (select descendant_id from ancestor where ancestor_id=1 and distance>0);
Who are the guys?
select decendant_id uncle from ancestor where distance=1 and ancestor_id in (select ancestor_id from ancestor where distance=2 and not exists (select ancestor_id from ancestor where distance=1 and ancestor_id=uncle) )
You avoid all the problems with joining a table to yourself through subqueries, the general limitation is 16 subsuqeries.
The problem is that saving the ancestor table is very difficult - it is best to execute the stored procedure.