RE: data model problem
- From: "Henry Poras" <henry@xxxxxxxxxxxxxxx>
- To: <rjsearle@xxxxxxxxx>
- Date: Wed, 6 Dec 2006 12:41:27 -0500
The logic is about right, except for the multiple parent logic which would
be 'if one parent is marked for deletion then child='X''.
The question is what is the cleanest way to implement this? Maybe an update
trigger on child.p_state and another one on child.c_state.
Multiple parents on the child just means multiple FK constraints on the
child table. No need for m-m relationships.
Thanks.
Henry
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of rjsearle@xxxxxxxxx
Sent: Wednesday, December 06, 2006 12:09 PM
To: henry@xxxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: data model problem
I think the issue may be lurking in your statement that a child may have
more than one parent. How do you model this? this sounds like a m-m
relationship that could be resolved with a resolution entity. not sure if
that addresses your concern
Also, a child can be marked for deletion independantly of the parent. Is
this true? If so then (ignoring multiple parents for a minute) I imagine
that if a parent is marked for deletion then the child is assumed to be
marked for delition. (correct?) If so then if parent = "X" then assume
child = "X" (ie override child value) else rely on child value
This is further complicated by the many parent scenario in which case I
suspect that the business rule is a little different:
if all parents are marked for deletion then
assume that child is also marked for deletion (over-ride child attribute)
else
rely on child attribute
fi
Does this help?
Russell
On 12/7/06, Henry Poras <henry@xxxxxxxxxxxxxxx
<mailto:henry@xxxxxxxxxxxxxxx> > wrote:
I've being talking with some of our developers about a possible data
inconsistency problem. It basically arises through deletion via setting a
field 'marked for deletion' instead of running the DML DELETE. Here is the
current setup.
We hava the following tables:
PARENT
parent_id (PK)
p_state ('A' active or 'X' marked for deletion)
CHILD
child_id (PK)
c_state ('A' or 'X')
parent_id (FK)
The child can have more than one parent, but I'll skip that case for now.
If the parent is marked for deletion (parent.p_state='X') the child must be
marked for deletion (child.c_state='X'). The child can also be marked for
deletion independently from the parent. There are three ways I can think of
marking the parent's state in the child table
1. a trigger which updates child.c_state whenever parent.p_state changes
2. set child.parent_id to NULL and worry about consistency in the child
table in the next step
3. add p_state to the child table and include it in the FK.
Cases 1 & 2 both require additional code in order to keep the two tables in
sync in the event of an UPDATE to the CHILD (or PARENT) table (i.e .set
c_state='A' even though the p_state is still 'X' needs to be made
unacceptible). Method 3 avoids this problem.
Now comes the issue of having c_state consistent with p_state (or pA_state,
pB_state, .). This structure is clearly non-normalized as c_state is
functionally dependent on child.parent_id (or child.p_state), one non-PK
field dependent on another.
Just trying to think of the cleanest way to handle this (effective dates??
Yuck). I'm sure this isn't unique to us. Any ideas out there?
Thanks.
Henry
Other related posts:
