Arbitrary depth table relationships

Ye' old general discussion board. Basically, for everything that isn't covered elsewhere. Come here to shoot the breeze, shoot your mouth off, or whatever suits your fancy.
This forum is not for asking programming related questions.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Arbitrary depth table relationships

Post 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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Arbitrary depth table relationships

Post by VladSun »

Some kind of tree?
There are 10 types of people in this world, those who understand binary and those who don't
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Arbitrary depth table relationships

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: Arbitrary depth table relationships

Post by Weirdan »

It's definitely a tree, stored as adjacency list
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Arbitrary depth table relationships

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Arbitrary depth table relationships

Post by alex.barylski »

Adjacency list model -- nice thanks you both :)
Post Reply