Should I delete child objects from the client, or via a stored proc

Question

I am working on both parts of a data layer. I get to create the procs, and the business object layer that will consume the procs.

Is there a best practice for determining where a delete should occur?

Here's the scenario. I have a table Book with foreign key to a child table Pages. If I delete the book, I also want to delete the pages. If the deletion of a page fails, I want the whole book to stay (transaction).

Should the delete occur in managed code (by creating a transaction, deleting all child objects and finally the book), or in the proc (again in a transaction)?

Or does it matter?

Answer 1

well there are 3 ways for this:

1. do it in the app with TransactionScope
2. do it in the proc
3. create a cascade delete on the PK-FK relationship.

depending on what your DAL looks like you'll have to make a choice. if it's a ORM then go with 1. else go with 3.

Answer 2

Call me old fashioned but I would always let a database do what a database does; let it take care of the deletion and return a value back to the calling code to indicate the success or failure of the operation.

I know that ADO.NET does a good job of managing SQL operations but I normally limit this to configuring the connection, adding the parameters and then running a stored procedure.

Answer 3

My preference is to perform child deletes from managed code. It is easier to ensure that all deletes are performed identically if done from here and it gives one easy place for another developer to come and understand how this entity works.

Answer 4

I would say it depends where you store your "logic" for the data.

1) If your stored procedures are mostly "dumb" ones with simple Inserts/Update/Delete. I would put the deletes in the data layer object where you would have more complex code.

2)IF you have written many complex stored procedure, where you need to check biz rules. Keep the logic all in the SPs and keep the data layer simple.

Really it where you want the complexity to lie. Don't put too many rules in both the objects and the SPs, or else maintenance will be a bitch.

Answer 5

If child objects should always be deleted when the main object is deleted the best place to do that is in the database. There are deltes that may happen from imports or queries that are not done through the GUI. Think about what would happen if you needed to deelte all the books from a publisher that went out of business. No one would do that one at a time through the GUI. Therefore if cascading delete is an option in your database, then set it. If not do it through a trigger.

Before doing this though, be very very sure that you want to delete the child objects. Will you lose historical data that you need? Often it is better to mark the parent record as inactive than to delete it. This way you don't lose the history on the book orders because you delted all the child records when the book was no longer available. You almost never want to delete the record if your system involves, ordering, warehousing, or financial systems or any other system where child tables would hold records that have a date history and which may need to be called in reports or when researching a customer call.