- PostgreSQL 9.1
I'll try to illustrate the problem here as simply as possible:
I'm trying to record the following relationship:
Node 1 => Node 2 => Node 3
Where Node 1 is the parent of Node 2, and Node 2 is the parent of Node 3. In the database table, the initial design is tracking this data in a linked list fashion. However, I am finding this extremely inefficient because each time I want to trace the lineage of say Node 1, I'd have to retrieve Node 2, then with Node 2, I'd retrieve Node 3. Since I would be displaying the lineage of Node 1 to the user, I'd have to make 3 separate calls to the database to display this information.
I'd also like to backtrack, but for the sake of simplicity I left that part out.
Some alternatives I have considered, but I can't go to at this time are Neo4j and other solutions like it. Unfortunately, at this time, I have to stay in Postgresql.
Is there a design pattern such that I can retrieve all this information at once? If there is, how would you implement with the example above?
With @MichaelT's comment, and finding Common Table Expressions, I've been lead to this link, which seems to provide more information on this: http://stackoverflow.com/questions/53108/is-it-possible-to-make-a-recursive-sql-query