1

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.

TMNuclear
  • 1,175
  • 5
  • 25
  • 49
  • Do you really need cascade and fk's on history table. From what i see if you delete goods, then records from history also deleted, not marked as deleted in Col4 – fly_ua Dec 16 '14 at 08:48
  • PK's won't be updated so for that reason they really don't. But once a Good gets permanently deleted it needs to be deleted from the history as well – TMNuclear Dec 16 '14 at 08:51
  • 1
    As workaround i can suggest inserting 2 records for replace, having column for self-reference to another record. – fly_ua Dec 16 '14 at 08:54
  • I have tried that as well, I forgot to mention. SQL also don't allow Cascade upon self reference. So I'm forced to delete in the right order or a trigger on the history table – TMNuclear Dec 16 '14 at 09:00
  • 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. – fly_ua Dec 16 '14 at 09:09
  • I guess that's the only option left indeed, haven't thought about it. Can you post this in a form of an answer so I can close the question? And thank you, I will go with this one. – TMNuclear Dec 16 '14 at 09:15

1 Answers1

1

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.

fly_ua
  • 1,034
  • 8
  • 12