Hello,
I have found a problem today to which I am unable to find the solution.
I write to this list looking for help.
I have been using PostgreSQL for about a year or so, and I manage a
quite large database. I usually design extensions to it, create new
tables and views, indexes, and many more. In short, it's not my first
database application.
I would like to store a tree in the database. A tree much like a
directory tree, with un unknown depth.
However, in my case, the order of the leafs (left to right) is
important.
I tried to implement the tree as a table:
CREATE TABLE tree ( father_id int, son_id int );
Then, I can easily find all sons for a father. Even in correct order:
SELECT son_id FROM tree WHERE father_id=1234 ORDER BY son_id;
Even if I wish to use another ordering, I could:
CREATE TABLE tree (father_id int, son_id int );
CREATE TABLE people ( person_id int, name text, age int );
and then
SELECT people.person_id, people.name, people.age FROM tree, people WHERE
tree.father_id=1234 AND tree.son_id=people.person_id
ORDER BY people.age;
For one generation, all works well. I could also extend that up to 2
generations, but not until the sons have no more sons. Could someone
help me find a way to output the data in the following way ?
Peter, 90
John (Peter's son), 65
Richard (John's son), 44
William (John's son), 45
Philip (William's son), 20
Tony (Peter's son), 70
Other (Tony's son), 50
Two things are crucial: ORDER and MULTIPLE GENERATIONS.
The genealogic example is given only to avoid explaining the complexity
of our application design. We are a company specialized in web server
performance monitoring, so genealogic studies is not our core business
;-) This way, I thought I could simplify the understanding of the
problem.
I am certain that others have been faced to similar problems, so
probably someone may help me.
Thank you all for any lights you can shed on this problem.
Antonio Fiol
P.S. Other similar problems I can think of:
Relation Boss --> Employee (though depth is finite in this case).
History of people owning an object (though only one "son" per "father",
so no ordering issue)
A directory tree (without any files).