Originally posted by: kamper
Originally posted by: EagleKeeper
Have a table that has two three fields.
First field is the primary node
Second field is for any parent nodes.
Third field is for any child nodes.
Two problems with that:
-putting multiple values in a single field is not a good thing
-duplicating the data about parent/child relationship is a bad idea. What if record A thinks it's the parent of record B but record B doesn't think it's a child of record A?
My suggestion is that you look at this more like a directed graph than a hierarchy, if that's appropriate. Have a table with all the info you need in it but with no information about relationships. Have a second table with two fields called "parent" and "child" and make them foreign keys back to the primary key of the main table. The one problem I see with that is that there's no guarantee against loops, but that's a pretty hard thing to do with a relational database anyway, I think.