Page 1 of 1

Arbitrary depth table relationships

Posted: Tue Feb 24, 2009 12:52 pm
by alex.barylski
When you have a table like so:

Code: Select all

 
pkid, rootid, caption
 
1, 0, Root
2, 1, Child
3, 2, Grandchild
 
Is there an existing name/pattern which describes this approach? Something similar to EAV?

Re: Arbitrary depth table relationships

Posted: Tue Feb 24, 2009 4:48 pm
by VladSun
Some kind of tree?

Re: Arbitrary depth table relationships

Posted: Tue Feb 24, 2009 8:41 pm
by alex.barylski
The result is certainly a tree structure but the implementation is relational so I wonder if it qualifies, if so, which one?

In storing ID's as opposed to pointers to other nodes, and the fact traversal of child nodes requires a different SQL statement than moving backwards somehow disqualify the technique as as of the above data structures.

In this sense it is more akin to a singly linked list as only the parent ID is stored in each node, however forward traversal is possible thanks to the power of SQL.

Re: Arbitrary depth table relationships

Posted: Wed Feb 25, 2009 2:30 am
by Weirdan
It's definitely a tree, stored as adjacency list

Re: Arbitrary depth table relationships

Posted: Wed Feb 25, 2009 4:29 am
by VladSun

Re: Arbitrary depth table relationships

Posted: Wed Feb 25, 2009 5:42 pm
by alex.barylski
Adjacency list model -- nice thanks you both :)