Search code examples
sql-server-2008historyself-referenceself-referencing-table

SQL Server history tracking of a table with multiple same referencing column

In my database design, I'm tracking history with triggers. First of all, I have a base table Goods with the following columns:

Tablename: Goods
COLUMN1PK: GoodID
COLUMN2: ArticleCode
COLUMN3: Amount

I want to track all operations made on the goods, like insert, delete or replace (not necessarily the changed values of the columns) . There can be an insert, an update, a delete or a replacement of a good. For example, GoodID 1 has reached it's lifetime and getting replaced by GoodID 2. To be able to capture this information, I can't see any other option than having a second column in the history table referencing to the same GoodID from the table Goods

My history table would be:

Tablename: GoodsHistory
COLUMN1PK: GoodHistoryID
COLUMN2: ChangedBy
COLUMN3: DateChanged
COLUMN4: ActionType (like inserted, updated, replaced or deleted)
COLUMN5FK: GoodID (capture any insert/update/'fake'-delete operation)
COLUMN6FK: ReplacedByGoodID (capture the `GoodID` that is the replaced `GoodID`)

See this record-set for an example of history data:

COLUMN1PK:  |  1          |  2           |  3           |  4
COLUMN2:    |  User1      |  User1       |  User1       |  User2
COLUMN3:    |  10/12/2012 |  12/16/2014  |  12/16/2014  |  12/16/2014
COLUMN4:    |  Inserted   |  Inserted    |  Replaced    |  Inserted
COLUMN5FK:  |  1          |  2           |  1           |  3
COLUMN6FK:  |  NULL       |  NULL        |  2           |  NULL

Short explanation: GoodID 1 exists since 2 years, and today User1 created a new GoodID and replaced the old one with the new one. Also, a User2 created a new GoodID 3.

I'm working in SQL Server 2008 R2, and SQL don't like multiple cascading paths. The cascading path exists because COLUMN5FK and COLUMN6FK both refer to GoodID. Only 1 of the two relationships allow a Cascade while the other is forced to take No action upon Update/Delete operations, resulting in non delete-able Goods if both FK-relationships's are set. (So in this case, I cannot update/delete GoodID 1 and 2.)

A workaround would be a trigger. Since I have a few tables that have the same setup, this would require me to create/edit triggers for each table that tracks history this same way, and I'm afraid of additional overhead as well as managing problems upon database schema changes.

So my question is actually if there are other, maybe smarter design/solutions for this kind of information gathering, or if I should stick with this one due to the limitations of SQL Server.

Solution

  • IMHO Than you need something more than one table for history, First will keep records for your goods, and main table will have references. e.g. 

    GoodsActions {Id, GoodId, Action} 
GoodsHistory {UserId, GoodsActionsId, SetId}

    where SetId will aggregate single history entry for logical operations.