Handling deletion and renaming of relational records

Question

I'm developing a relational system, that involves the following entities:

• Enquiry
• Quote
• Supplier
• Town
• Vehicle

Enquiry is the main model for the system and contains foreign keys for all the other entities.

Now what I'm concerned of is for example if the client goes and deletes a Town - the Enquiry record has a reference to the town id and would therefore break the system. Similarly if the client goes and deletes a Vehicle then Supplier records would break.

So what is the best way to handle deletions of relational records? Should we even offer the facility to delete records (perhaps have a enabled/disabled boolean switch instead?).

Similarly when renaming records how can we preserve original data for older records (for example if the client decided to rename Vehicle "Bus" to "Minibus").

Answer 1

If you want to handle this at the database level, Foreign Key constraints with Cascading Updates and Deletes is what you are looking for. For handling this within your application, use an ORM, like Doctrine.

Answer 2

what is the best way to handle deletions of relational records?

Only allow deletions of orphaned records? If I delete records from ENQUIRY that relate to a specific Vehicle, then someone should be able to remove that vehicle. But not before the child references have been dealt with... Pretty easy to handle using NOT EXISTS:

DELETE FROM VEHICLES
 WHERE NOT EXISTS(SELECT NULL
                    FROM ENQUIRY e
                   WHERE e.vehicle_id = VEHICLES.vehicle_id)

---

Similarly when renaming records how can we preserve original data for older records (for example if the client decided to rename Vehicle "Bus" to "Minibus").

This is why you make the name/description separate from the primary key for using as a foreign key. If the VEHICLES table has two columns--vehicle_id and vehicle_description--then the description can change without referential integrity impact because you define the foreign key on the vehicle_id column.

Answer 3

There are several issues you have raised with this post. First, it may be beneficial to allow users with low permissions to soft-delete a record by toggling a isLive column as you indicated with a bit value. Secondly, you might benefit from the creation of a shadow table that will be used to record CRUD operations performed by the user. Through this, you will be able to record WHO made the change, WHAT type of change (Create/Update/Delete), WHERE the change was made (Table A, Column 1). Thirdly, you will want to handle the deletion of values bound by a foreign key carefully. You must delete all related connections prior to deleting from the source table. So you would delete the record in Enquiry before deleting the record in Town. Hope this helps..

Answer 4

This is a common problem. The user wants to delete 'Bus' because they don't use it any longer and don't want to see it on the Enquiry form. But you need the record because it's tied to five hundred older Enquiries.

Add an enabled/disabled or active/inactive flag to the record. Allow the user to disable records when they want. (Also allow them to reenable the records when they realize they were wrong.) Don't show disabled records on the Enquiry form, but keep them in the database to be able to show older data properly.

If you like, you can also allow deletion (actual removal from the database) if there is no Enquiry tied to the record. I personally don't show a delete button unless I'm sure a record can be deleted because I find users get frustrated if they can click the delete button but then are told they can't delete. So I check whether the record can be deleted before giving the option.

The problem of a user editing a record and changing the value from 'Bus' to 'Minibus' or from 'ACME Wallpaper, Inc.' to 'XYZ Catering' is one you have to live with. User education and making it easy to add new records is the only real way to handle it. Users have to be taught that ADDING and EDITING are different. Sometimes they have to be reminded with help-text on forms, warning messages, etc. I'm unaware of anything you can do programmatically that can accurately distinguish between a correction to a field, and a total change to the field.