Re: Recursive join - blind alley?

"Mike MacSween" <mike.macsween.nospam_at_btinternet.com> wrote in message news:3ff67cc9$0$52888$5a6aecb4_at_news.aaisp.net.uk...
> 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

Well it won't take you long to design a table of elements: ElementID, ElementName, ParentID, etc with each element having a parent in the table. But before you go too far finalising the structure - consider the basic issues of getting the data into the database and extracting it later. For example, how does the user create a new production with different elements and then link musicians and others to these elements? What will a print-out look like? Do you intend to make use of some form of activex tree-control or do you have another way to show this hierarchy on a piece of A4 paper?

I personally think that unless you have had a lot of experience with product codes and fully appreciate the benefits of masking techniques with analysis codes, then you should further investigate this before dismissing it. There is a genuine reason why so many databases use this method - even if you use a treeview control, even if your elements table has a parentID column, even if you hide this code from the user and use VBA to build it up. Using masking provides an extremely fast and efficient way to work with this sort of data.

Alternatively, quickly build the elements table (the proposed design is pretty straight forward) and then try to construct a couple of key forms and reports around it. Let us know how you get on.

Fletcher Received on Sat Jan 03 2004 - 13:27:29 CET