storing sibling relationships in a database.

[Martin]

parent-child relationships come naturally to databases, but what about parent-less sibling relationships?

Consider a table with the fields id (a PK) and componentName. I can think of two ways around the problem:
1. have an extra field which stores the component's siblings id's. For example if row 1 is the sibling of rows 3, 5 and 6, it would look like
id: 1
componentName: Comp
siblings: 3;5;6

the downside of this approach is that the relationships aren't really enforced and everything is up to the code.


2. have another table which stores siblings. Using the same example, row 1 would be
id: 1
componentName: Comp

another table would store
id:1
component: sibling3
id:1
component: sibling5
id:1
component: sibling6
id:3
comp: sibling1
id:3
comp: sibling 5
id:3
comp: sibling6.

this would make the relationships more explicit, but woudl lead to lots of data duplication.



So what do you say? is there a better way? which of these two is the better?

 

[notfred]

You store the parent ID, and then you select all the children with the same parent.

 

[pontifex]

incest is bad. stop hiding it and stop doing it.

 

[mundane]

If you have sibling groups, you must have some kind of implied parentage (or at least common grouping). If the sibling relationship is symmetric and transitive, use a sequence generator to create new group IDs for each of the sibling groups.