Re: Recursive join - blind alley?

OK, still going at it and testing my ideas in public.

Seems to me that in the 'Element' table what's required is a 'Level' field. 1 being the top, 10 (for instance) being the bottom. With a few validation rules. An element at level 1 can't have a parent, at level 10 can't have a child. A child must have a level that is parent level+1.

That imposes a few restrictions. Children can't have more than one parent. That's a requirements issue, I'm awaiting a response from the client. There can't be more than 10 levels. Although the structure of the recursively joined table _theoretically_ allows infinite levels, in this app that won't be the case. It's perfectly possible to imagine saying to this client, or the similar clients its aimed at 'look, you can't have a structure more than 10 levels deep'. Or 5 or 20. It would be in that range. Whereas with a complex BOM there might be a far taller tree. The important thing in this app is that it is variable. From a single event to 10 nested sub events.

The level number might make a lot of SQL easier. You'd know how many sub queries to search from top to bottom, if the bottom was at level 4, for instance. I'm guessing.

Actually I don't thing the nested sets BOM does it. It just models _one_ thing. That's not what I want. I need more than one root node.

Yours, Mike MacSween Received on Sat Jan 03 2004 - 09:25:31 CET