Re: difficult design question

On Sun, 25 Nov 2007 20:29:12 -0800 (PST), meislerj_at_yahoo.com wrote:

>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

You call this difficult? :)

What have you tried so far?

B. Received on Mon Nov 26 2007 - 09:04:37 CST