Customer wants some data to appear after you later delete rows. System giant / not my creation. Fast but safe approach? Name / class of design problem?

Question

This is a fairly common problem, it probably has a name, I just don't know what it is.

A.) User sees obscure piece of information in Row B of L_OBSCURE_INFO displayed on some screen at a certain point. It is in table L_Obscure_info.

B.) Under certain circumstances we want to correctly delete data in L_OBSCURE_INFO. Unfortunately, nobody accounted for the fact that the user might want to backtrack and see some random piece of information that was most recently in L_OBSCURE_INFO.

C.) The system is enormous and L_OBSCURE_INFO is used all the time. You have no idea what the ramifications are of implementing some kind of hack and whatever you do, you don't want to introduce more bugs.

I think the best approach would be to create an L_OBSCURE_INFO_HISTORY table and record a record in there every time you change data. But god help your ensuring it's accurate in this system where L_OBSCURE_INFO is being touched everywhere and you don't have time to implement L_OBSCURE_INFO_HISTORY.

Is there a particularly easy, clever design solution for this kind of problem -- basically an elegant database hack? If not, is this kind of design problem under a particular class of problems or have a name?

Answer 1

Use a database table trigger to maintain your history in a second table.

Answer 2

If seeing the history of deletes (and I would assume updates) is the requirement, the history table you described is likely your best option.

If, as you say, the table is "being touched everywhere" and you can't control writing of the history via application code, you will have to implement a database trigger that essentially snapshots the row each time a change is made (or only deletes, if that is the limit of your need).

Another option might be to add an "is_active" column in the table, and instead of allowing deletes, allow users to inactivate rows. This is effectively a "soft delete", which is not always a good practice, because filtering out inactive records in every query is a pain.

If you DO have the option of implementing a solution in application code (i.e. no one is touching data except through the application), you could implement a logging mechanism to record the state of a row that is about to get deleted.