Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> difficult design question
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 Received on Sun Nov 25 2007 - 22:29:12 CST