Re: Tree (forest) design

"Robert Stearns" <rstearns1241_at_charter.net> wrote in message news:403E8CA0.1010505_at_charter.net...
> Mikito Harakiri wrote:
> > "Robert Stearns" <rstearns1241_at_charter.net> wrote in message
> > news:403E7404.2040200_at_charter.net...
> >
> >>I am working on a design problem where I have many (possibly
> >>multinational) customers with tree structured administrative structures
> >>(cust, nation, region, site, building for instance). At the leaf nodes
> >>of the administrative tree, there is a tree organized location system
> >>(building, floor, room, rack, bin for instance).
> >
> >
> > Why <building, floor, room, rack, bin> is a tree?
>
> Buildings have multiple floors, each having multiple rooms etc. Each of
> these entities may contain inventory items, and I want to be able to
> reference the inventory on any level, including (or not) the sublevels.
> For instance if the inventory referred to a manufacturing facilities,
> screws might be in bins, engine blocks on racks, body panels in rooms,
> etc. If you are dealing with car dealerships, the divisions might be the
> dealership, (branch (lots, buildings (rooms))).

I would have a table with the granular data that includes foreign key attribute(s) that identify the position in the tree. Then whatever data you need to collect about non-leaf nodes in the tree could be considered "code file" type of information (to which the foreign key(s) point). There are several options for this non-leaf hierarchy, which I'm guessing you already know, but what I think is useful for sanity with an RDBMS is to separate out the leaf nodes as a "master file" of sorts. This is based on experience and not theory, however, so I'm curious what the purists think. Cheers --dawn Received on Fri Feb 27 2004 - 01:48:47 CET