Re: Recursive Queries?

>> And your solution to this? <<

One of the hierarchical designs given in TREES & HIERARCHIES IN SQL
(ISBN: 978-1-55860-920-4). I happen to like the nested sets model
because it ports and gives very easy hierarchical aggregations (BOM,
accounting reports, etc as opposed to path searches).

You might also want to look at http://www.dbazine.com/ofinterest/oi-articles/celko35/view?s earchterm=Celko

The article gives a declarative way to enforce Transition Constraints,
which might help if your process steps have to come in some order
("bake the beard before you slice it", etc.)

Re: Recursive Queries?

On Mon, 31 Mar 2008 10:28:57 -0700 (PDT), --CELKO--
wrote:

>>> And your solution to this? <<
>
>One of the hierarchical designs given in TREES & HIERARCHIES IN SQL
>(ISBN: 978-1-55860-920-4). I happen to like the nested sets model
>because it ports and gives very easy hierarchical aggregations (BOM,
>accounting reports, etc as opposed to path searches).
>
>You might also want to look at http://www.dbazine.com/ofinterest/oi-articles/celko35/view?s earchterm=Celko
>
>The article gives a declarative way to enforce Transition Constraints,
>which might help if your process steps have to come in some order
>("bake the beard before you slice it", etc.)


I have looked at the article, but I don't think the dataset described
is a match for my problem. I have two different entities with
differing requirements for much of the data held within them, that
have the same effect on one part of the system.

A sales line item is comparable with a production stock requirement
item only at the level where it effects the allocation of physical and
incoming material (the case described here). The other data required
to be held for each of these items is radically different. As is the
order header information for sales and procurement orders. I am unsure
where this fits in a hierachical data structure.