Best way to design a database and table to keep records of changes?

I need to setup a history feature on a project to keep track of prior changes.

Let's say I have two tables right now:

NOTES TABLE (id, userid, submissionid, message)

SUBMISSIONS TABLE (id, name, userid, filepath)

Example: I have a row in notes and the user wants to change the message. I want to keep track of it's state before the change and after the change.

What would be the best approach to setting up a column in each of these tables which will say if an item is "old." 0 if active OR 1 if deleted/invisible.

I also want to create a history (AUDIT TRAIL) table which holds the id of the prior state, the id of the new state, which table these id's relate to?

When designing versioning capabilities in your data, there are several minimal (I would think) requirements:

• Each version of the data should be self-contained and independent of other versions. This means no flag or other indicator showing which is the current version and which are "history." It also means updating the entity means inserting a new version only -- no updating of previous versions needed.

• Avoid what I call Row Spanning Dependency. That is where one field (End_Date) of a row must remain in synch with another field (Start_Date) of a different row. This makes working with the data more difficult and is an excellent source of anomalies.

• The current version and all past versions should be in the same table. This makes it possible to use the same query to view past data "as of" a particular date and to view the current data.

• Foreign keys to data that has been versioned should work the same as normal (unversioned) data.

• The design should be so simple or universally understood that the learning curve for new developers is minimized.

Here are the slides of a presentation I have made a few times at tech fairs. It covers how all the above can be done. And here is a document that goes into more detail. I must make apologies for the document -- it is a work in progress and not all sections are completed. But it should give you all the information needed to implement anything from simple versioning to full-on bi-temporal access.

StackExchange Database Administrators Q#114580, answer score: 19