A Never delete a relational DB schema structure

Question

I am considering the possibility of creating a database relational schema for a database that does not actually delete anything (sets a remote flag or something else).

1) What metadata columns are commonly used to host such an architecture? Obviously, a logical flag for IsDeleted can be set. Or maybe only the timestamp in the "Deleted" column works better, or perhaps both. I'm not sure which method will cause me more problems in the long run.

2) How are updates usually updated in such architectures? If you mark the old value as deleted and enter a new one, you will encounter inconsistent PK restrictions (for example, if you have a PK column identifier, then the new row must have the same identifier as the one you just designated as invalid, or otherwise all your foreign keys in other tables for this identifier will be useless).

Answer 1

Here are some additional questions you might also want to consider.

• How often is the removal. What is your performance budget? This may affect your choice. The answer to your design will differ depending on whether the user deletes one line (for example, allows you to answer the Q & A question, as well as delete entries by the clock from the feed)

• How are you going to show deleted records on your system. This is for administrative purposes only or any user can see deleted records. This matters because you probably need to create a filtering mechanism based on the user.

• How foreign key constraints will work. Can one table link to another table where there is a remote record?

• When you add or modify existing tables, what happens to deleted records?

Typically, systems that care a lot about auditing use tables that Steve Prentis talked about. It often has every field from the source table with all restrictions disabled. It will often have an action field to track updates and deletions, and also indicate the date and timestamp of the change along with the user.

For an example, see the PostHistory table at http://data.stackexchange.com/stackoverflow/query/new

Answer 2

If your goal is auditing, I would create a shadow table for each table. Add the triggers that trigger when updating and deleting, and paste a copy of the row into the shadow table.

Answer 3

I think that what you are looking for here is usually called "knowledge acquaintance."

In this case, your primary key will be your regular key plus the date the knowledge began.

Your end date can be either zero for the current record, or an hourly "time end".

When updating, you usually set the end date of the current record to “now” and insert a new record, starting from the same “now” with new values.

In "delete", you simply set the end date to "now."

Answer 4

• I have done it.

2.a) the version number solves the problem with a unique constraint, although it really just relaxes the uniqueness, doesn't it.

2.b), you can also archive old versions to another table.