Versioning in the database

Question

I want to store full versioning of the row every time a update is made for amount sensitive table.

So far, I have decided to use the following approach.

1. Do not allow updates.
2. Every time a update is made create a new entry in the table.

However, I am undecided on what is the best database structure design for this change.

Current Structure

Primary Key: id

id(int) | amount(decimal) | other_columns 

First Approach

Composite Primary Key: id, version

id(int) | version(int) | amount(decimal) | change_reason 
 1      | 1            | 100             | 
 1      | 2            | 20              | correction

Second Approach

Primary Key: id

Uniqueness Index on [origin_id, version]

id(int) | origin_id(int) | version(int) | amount(decimal) | change_reason 
 1      | NULL           |  1           | 100             | NULL
 2      | 1              |  2           | 20              | correction

Answer 1

Why you are not going for SCD-2 (Slowly Changing Dimension), which is a rule/methodology to describe the best solution for your problem. Here is the SCD-2 advantage and example for using, and it makes standard design pattern for the database.

Type 2 - Creating a new additional record. In this methodology, all history of dimension changes is kept in the database. You capture attribute change by adding a new row with a new surrogate key to the dimension table. Both the prior and new rows contain as attributes the natural key(or other durable identifiers). Also 'effective date' and 'current indicator' columns are used in this method. There could be only one record with the current indicator set to 'Y'. For 'effective date' columns, i.e. start_date, and end_date, the end_date for current record usually is set to value 9999-12-31. Introducing changes to the dimensional model in type 2 could be very expensive database operation so it is not recommended to use it in dimensions where a new attribute could be added in the future.

id | amount | start_date    |end_date       |current_flag
1    100      01-Apr-2018    02-Apr-2018     N
2    80       04-Apr-2018    NULL            Y

Detail Explanation::::

Here, all you need to add the 3 extra column, START_DATE, END_DATE, CURRENT_FLAG to track your record properly. When the first time record inserted @ source, this table will be store the value as:

id | amount | start_date    |end_date       |current_flag
1    100      01-Apr-2018    NULL            Y

And, when the same record will be updated then you have to update the "END_DATE" of the previous record as current_system_date and "CURRENT_FLAG" as "N", and insert the second record as below. So you can track everything about your records. as below...

id | amount | start_date    |end_date       |current_flag
1    100      01-Apr-2018    02-Apr-2018     N
2    80       04-Apr-2018    NULL            Y

Answer 2

The best method is to use Version Normal Form (vnf). Here is an answer I gave for a neat way to track all changes to specific fields of specific tables.

The static table contains the static data, such as PK and other attributes which do not change over the life of the entity or such changes need not be tracked.

The version table contains all dynamic attributes that need to be tracked. The best design uses a view which joins the static table with the current version from the version table, as the current version is probably what your apps need most often. Triggers on the view maintain the static/versioned design without the app needing to know anything about it.

The link above also contains a link to a document which goes into much more detail including queries to get the current version or to "look back" at any version you need.

Answer 3

I would suggest a new table which store unique id for item. This serves as lookup table for all available items.

item Table:

id(int)
1000

For the table which stores all changes for item, let's call it item_changes table. item_id is a FOREIGN KEY to item table's id. The relationship between item table to item_changes table, is one-to-many relationship.

item_changes Table:

id(int) | item_id(int)   | version(int) | amount(decimal) | change_reason 
 1      | 1000           |  1           | 100             | NULL
 2      | 1000           |  2           | 20              | correction

With this, item_id will never be NULL as it is a valid FOREIGN KEY to item table.