Re: difficult design question

On 26 nov, 05:29, meisl..._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

Sounds like a Bill-of-Material implementation. (An item may exist of subassemblies, which in turn, can be subassemblies).

One extra business rule, which you did not write out: - an assignment can only belong to one goup. This can be solved by having a trigger, that checks whether an assignment already is part of a group, or work item, or by keeping track of groups/work items on assignment level (if it's NULL, you can assign them, if NOT NULL, it's prohibited)

how about regarding "group"assignments as work item? In that case, you would have a self-referencing table work_items Received on Mon Nov 26 2007 - 03:49:24 CST