14

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?

5

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.
  • what is the best way to apply 'Solution No. 2: Dedicated Data-Tracing Table' for an OLTP application. – AA.SC Sep 10 '15 at 11:53
  • The company I work for currently uses multiple schemas, one specifically for the Audit trail. The audit table really is quite a simple design when using Solution #2 (which is exactly what we use here at work). Break down the different task (Inventory table updated, Customer information updated or deleted, Credits provided towards customer, etc etc) and build the audit table based on common actions that users are capable of. Does that answer your question in regards to applying solution 2 to your db, if not please clarify. Thanks! – Hector Sep 10 '15 at 12:20
  • Actually we already auditing data with first approach using Audit Tables, but Auditing data is becoming so huge and now we want to convert our approach by just capturing data against changed columns. My question is how can I achieve this approach? what is the best way to track which column of the table is changed ? .. if a table has more than 20 columns one of them is with DataType Text. – AA.SC Sep 10 '15 at 12:37
9

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.

  • 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. – GMsoF Mar 26 '18 at 3: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 6:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.