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_at_itasoftware.com> 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
>

--
http://www.freelists.org/webpage/oracle-l