Re: difficult design question

meislerj_at_yahoo.com wrote in news:5d30763b-adac-42da-99e7-5cc593c42b43 @b40g2000prf.googlegroups.com:

> I have a design question I've been tossing around, but can't seem to
> come up with a good solution. Basically, I have a work item. Each
> work item can have one or more assignments. Or, it could have one or
> more "groups of assignments". Or, it could have a combination (that
> is assignments and groups of assignments). Each assignment in turn
> could have as its children one more assignments and / or groups of
> assignments, etc.
>
> Also, it is important to note, a work item could have 0 groups and 0
> assignments. That is, it doesn't need to have any children. Each
> group must have at least one assignments within it. Also, it is
> necessary to keep track of the "parent assignment" for each
> assignment, even if the child assignment is part of a group.
>
> So for instance:
>
> Work item
> |
> ----------------------------
> | | |
> GP1 AS1 AS2
> | | |
> ---------- GP2 AS3
> | | |
> AS4 AS5 -------------
> | |
> AS6 AS7
>
>
> The design I was leading towards was something as follows:
>
> A work item has as its child 0,1 or more Groups. A work item also has
> as its child 0, 1 or more assignments.
> A group has as its child 1 or more assignments.
> An assignment has as its child 0,1 or more assignments. An assignment
> also has as its child 0, 1 or more groups.
>
> The problem with this is that with this model, a group could
> theoretically contain an assignment form a different group. For
> instance in the example above, GP1, which should have assignments AS4
> and 5, could theoretically have AS6 as part of its group too.
> However, the business does not allow for this.
>
> 1 -- I am having difficulty coming up with an appropriate logical
> model.
> 2 -- How would I implement this in the Oracle database?
>
> Any help would be appreciated. Thanks!
>
> Jan
>

Third Normal Form Received on Sun Nov 25 2007 - 23:34:45 CST