 | |
difficult design question
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
difficult design question -
11-25-2007
, 10:29 PM
|
---------------------------- | | 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
#2
| |||
| |||
|
|
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 |
#3
| |||
| |||
|
|
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 |
(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
#4
| |||
| |||
|
#5
| |||
| |||
|
|
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 |
What have you tried so far?
B.
#6
| |||
| |||
|
|
meisl... (AT) yahoo (DOT) 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- Hide quoted text - - Show quoted text - |
#7
| |||
| |||
|
|
On Sun, 25 Nov 2007 20:29:12 -0800 (PST), meislerj (AT) yahoo (DOT) 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. |
or in the database?
B.
Quote:
|
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. |
#8
| |||
| |||
|
|
On Mon, 26 Nov 2007 10:04:37 -0500, Brian Tkatch <N/A> wrote: On Sun, 25 Nov 2007 20:29:12 -0800 (PST), meisl... (AT) yahoo (DOT) 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. I do not understand how this problem arises. Are you talking logially, or in the database? B. 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
is self-referencing. Also, the work item entity has a group entity as
a child. Additionally, the group entity has the assignment entity as
its child. I have a relationship in the other direction too, from
assignment to group to keep track of the parent assignment of each
group (a requirement to know the parent).
This all seems ok logically, but it allows for the out-of-sync
situation I described above. And, when implemented in the database, I
have shown that I can put assignments in groups where they shouldn't
belong. I know I can have my application handle this business
requirement, but I'd like to model it, and have the database handle it
if possible.
#9
| |||
| |||
|
|
On Nov 26, 10:12 am, Brian Tkatch <N/A> wrote: On Mon, 26 Nov 2007 10:04:37 -0500, Brian Tkatch <N/A> wrote: On Sun, 25 Nov 2007 20:29:12 -0800 (PST), meisl... (AT) yahoo (DOT) 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. I do not understand how this problem arises. Are you talking logially, or in the database? B. 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.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - I have created a work item entity with a child assignment entity which is self-referencing. Also, the work item entity has a group entity as a child. Additionally, the group entity has the assignment entity as its child. I have a relationship in the other direction too, from assignment to group to keep track of the parent assignment of each group (a requirement to know the parent). This all seems ok logically, but it allows for the out-of-sync situation I described above. And, when implemented in the database, I have shown that I can put assignments in groups where they shouldn't belong. I know I can have my application handle this business requirement, but I'd like to model it, and have the database handle it if possible. |
Thanx for explaining. I do not understand it well enough, but i can
make some guesses. If you did put together the TABLEs already, posting
the SQL scripts might prove to be very helpful.
My understanding is, you are trying to associate two children from the
same parent:
Parent: Id
Child1: Id, Parent
Child2: Id, Parent, Child1
The problem being that Child_2 can be associated directly two
different Parents. One directly and one indirectly through Child1.
If that is the problem, a solution is simple. The FOREIGN KEY on
Child2 should include the Parent's id from Child1.
B.
#10
| |||
| |||
|
|
This all seems ok logically, but it allows for the out-of-sync situation I described above. And, when implemented in the database, I have shown that I can put assignments in groups where they shouldn't belong. I know I can have my application handle this business requirement, but I'd like to model it, and have the database handle it if possible. |
"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)"
was misunderstood?
--
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.