Re: data model problem

Underlying all of this is the assumption that there is a really good reason for not using a "DELETE FROM ..." dml statement. I'm curious, why not just delete it? It avoids a world of pain later on.

Anyway, see below...

It's difficult to address this with abstracts, do you care to send me some specifics and I can recommend something to suit the specific problem.

Russell

On 12/7/06, Henry Poras <henry_at_itasoftware.com> wrote:
>
> The logic is about right, except for the multiple parent logic which
> would be 'if one parent is marked for deletion then child='X''.
>

What does this mean for the remaining parents? Are they now to be childless and is that meaningful?

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

There's a p_state on child? That was not in your original schema. Did you just add that to resolve this situation?

Multiple parents on the child just means multiple FK constraints on the
> child table. No need for m-m relationships.
>

Multiple FK constraints to the one parent table???? Huh? Ohhhh, you mean FK constraints to several other tables... (correct?) I think this is dangerous and a potential cause of data anomolies. Is it possible for the relationships to some tables to be derived from other relationships? This will result in a hierarchy of relationships and may more meaningful in business terms

Thanks.
>
> Henry
>
>
> -----Original Message-----
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *rjsearle_at_gmail.com
> *Sent:* Wednesday, December 06, 2006 12:09 PM
> *To:* henry_at_itasoftware.com
> *Cc:* oracle-l_at_freelists.org
> *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_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