26

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?

Kin Shah
  • 62,225
  • 6
  • 120
  • 236
Schwarz
  • 375
  • 1
  • 4
  • 6

3 Answers3

19

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.

TommCatt
  • 2,129
  • 10
  • 10
  • 2
    Very nice points! However, I don't quite understand this This means no flag or other indicator showing which is the current version and which are "history.", if no flag or indicator, how we differentiate the current version from the history version? Especially based on your third point which you suggest they should be in same table. – Sam YC Mar 26 '18 at 03:11
  • The presentation shows an example design, including the query to read current and/or past data from the tables. If it looks interesting enough to look into deeper, the document contains a lot more detail. – TommCatt Mar 27 '18 at 06:09
  • This was interesting, thanks. Why do both tables include the same columns? Shouldn't Employees just have EmpId and nothing more? My use-case includes an additional requirement -- to allow storage of 'custom fields' without the need to alter the table (in this case, 2 tables). Any recommendations for this added requirement? – danielb Jan 26 '23 at 09:42
  • Ignoring subtables created by other normalization, the versioned entity consists of two tables: the primary table consisting of the primary key and any fields that either do not change over the life of the entity (such as birthdate) or fields that are not necessary to version. There is no need to maintain multiple copies of data that never changes or may change untracked. – TommCatt Feb 09 '23 at 00:53
6

Please view

http://www.codeproject.com/Articles/105768/Audit-Trail-Tracing-Data-Changes-in-Database

It is a very good read on the approaches to create a Audit Trail in your database design. Audit trails are a necessary to the implementation of a database. You should always be able to see the actions of database users within the system.

We can track which rows were changed when in our PTA (Point in Time) system by adding some standard PTA (point in time) columns to all tables of PTA interest.

I suggest the following:

DateCreated – the actual date on which the given row was inserted.
DateEffective – the date on which the given row became effective.
DateEnd – the date on which the given row ceased to be effective.
DateReplaced – the date on which the given row was replaced by another row.
OperatorCode – the unique identifier of the person (or system) that created the row.
Hector
  • 1,032
  • 1
  • 8
  • 22
1

I'm going to go against the grain a bit and vouch for the following:

  1. Maintain the following columns: isActive (bit), createdAt, updatedAt, deletedAt, as well as createdAtBy (userId), updatedAtBy, deletedAtBy

  2. Whenever the backend wants to write into the table, it is forced to set isActive to false for all other records (here record does not mean the entire table, it pertains to the logical/domain subgroup of records in your table). This applies only for update and delete since for "create" there are no other records.

  3. When the record is updated, only the row with isActive = 1 should have its "updatedAt" set. You do this in a transaction with creating the new row -- the one that is now going to gain isActive = true.

  4. When the record is deleted, you set updatedAt AND deletedAt for the row with isActive = 1. After the delete is completed, there should be NO rows with isActive = 1.

The tradeoff with this approach versus the one above is that you do have to write more backend logic for your writes. However, personally, I much prefer having a simple flag "isActive true/false" in the long run.

Bobby Connolly
  • 111
  • 1