Should i allow deletion of child records ?? 2

[miq]

hi,

two tables are related with respect to primary-foreign key relation.

table1 primary table
table2 secondary table



my question is, should i allow user to delete record in a table1 which do have child records in table2. Ofcourse, for this i have to implement instead of delete trigger.
What is considerd a normal behaviour. Should he be refused to delete parent record and, if he really wants to delete it then he has to first delete all child records from table2 then he would be allowed to delete parent record from table1.


What do you think ???


Bye
miq

 

[MeanGreen]

By rules of normalization, you would have to delete all child records before deleting the Parent record. It really depends on WHY you are deleting the Parent record. If the record is no longer required, then why would you need the child records? Normal practice is to enforce cascaded updates and deletes, meaning when you update or delete the Parent, it applies the changes to your child records.

Hope this helps.

 

[phirst]

Hi miq,
The answer depends on the data to be kept in the tables.
If the tables were order and order-line, deleting the order should delete the order-lines.
If the tables were user-group and user, you should perhaps restrict the deletion of the user-group if there were still users in it. Thus allowing the program administrator time to change the user-groups of the users.
There is no fixed rule, it depends on the business logic of your application.
Cheers,
Paul.

 

[r937]

meangreen, normalization does not enter into enforcing integrity constraints -- the database actually does not understand normalilzation

also, the normal practice is RESTRICT rather than CASCADE, i.e. if you don't specify CASCADE then you get RESTRICT (do not allow delete to happen if child rows exist)

rudy

 

[MeanGreen]

Rudy,
While normal practice is RESTRICT, that does not make it the correct method. Normally RESTRICT is the normal to protect the innocent (ie. someone who doesn't know the concequences of Cascading changes). Conceptually, the reason for having foreign key relations is to keep all data capable of linking to another source. If your business model says that when you change a KEY item you need to change the link,then cascading would be the ideal choice.
You are right that normalization does not enforce integrity constraints, however normalization does help ensure that by reducing duplication you are less likely to introduce integrity issues.

Thank you.

 

[miq]

hi guys,

let me explain myself clearly, I do know about cascading options as well. Main idea behind this post is a developer's perspective rather then administrator's.

I am working on a book store database and its client application. There may be times when a book is no longer publised in this senario is it appropriate to allow client application to delete entry from books table and all the related sales and other records (child records) be deleted automatically. But this should not be the general behaviour. i.e. no cascading should be configured but in special cercumstances client may be allowed such operaitons. I think it should be implemented through instead of trigger which can check for certain state of data before allowing cascading deletion.


Im i correct with my logic ??


bye
miq

 

[r937]

if the client wants it, the client should get it

never mind that once you have recorded a sale, it would be unwise (from an accounting perspective) to remove all evidence of that sale

perhaps your trigger could set a flag (active/inactive) instead of removing the records?

rudy

 

[MeanGreen]

I think from a design perspective you have 3 choices. The first choice is the one you are discussing now, which is when a book becomes obsolete remove it and all references throughout the database.
The second choice is rather then remove the entry, simply set a flag indicating that it is obsolete so no further reference is made in your standard interface.
The third choice would be to move the book to a table holding obsolete items. This follows along the line of keeping it around in case you need it, but helps reduce entries within your "working" table which you would access more frequently.
I would recommend that you never delete an obsolete book and its children, but flag it or move it. It really depends on the business rule that is put in place. But for argument sake, lets say you decide to remove all references. At least one time someone is going to follow "procedure" and mark the wrong book as obsolete and then your "trigger" will plod merrily along and delete all references to the book. Now you have to recover that book and all references because the user made a mistake and marked the wrong book. If you mark it obsolete or move it, then recovery is simply a matter of running a query to set everything back. You could even have a stored procedure to recover an obsolete book. If however you have deleted it, then you would probably have to restore a copy of the database, extract your necesarry information, and port it into the production database.
And if you mark the book as obsolete (or move it), then you can generate a history of books that have come and gone. If a client should come in and ask for one of the obsolete books, you could tell them when that book was pulled from the shelves. If the client offered some great incentive to acquire that book, then when you got it, you would not have to recreate a reference to it in your database.
This is just my view. I am sure others might think differently. With todays relational databases, setting the flag makes recovery a snap, and does not slow retrieval processes by much. As you can tell, I would opt to simply flag the instance as obsolete and use that flag throughout all my processes. By default, I would set the obsolete flag as false.

Hope this helps.

 

[miq]

yes, you guys are right when you say "deleting sales details is not appropriate". But for the sake of simplicity I adopting it. But now ...

History tables were in my mind but they require some extra management which is why i dropped it.
Flag thing is a new approch to me and it looks pretty attractive too. I agree with you both. I think i should use flag approach because its simple & simple.


Thank you all especially, r937 and MeanGreen (quite interesting nick). your reply were most helpfull stars for you



bye
miq