RE: data model problem

Henry,  

    Marking a field for deletion when it has no other benefit to the database is kooky at best. Why not run the DML directly from the application, and then add "on delete cascade" to the child's parent_id foreign key column. Keeps the data consistent & is very easy to implement.     

Dick Goulet, Senior Oracle DBA

45 Bartlett St Marlborough, Ma 01752, USA Tel.: 508.573.1978 |Fax: 508.229.2019 | Cell:508.742.5795

RGoulet_at_kanbay.com
: POWERING TRANSFORMATION  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Henry Poras Sent: Wednesday, December 06, 2006 11:21 AM To: oracle-l_at_freelists.org
Subject: data model problem

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