I'm designing an asset management database that tracks IT hardware. I decided to use a supertype/subtype design. I'm at a point where I want to track history of changes for devices. I wanted to use a separate history table, but I can't decide how to track history for changes made to subtype tables.
If I use separate history tables for each subtype table I can reconstruct records by joining them with the supertype history table, except in the case where subtype history tables change independently of the supertype history table. By independently, I mean there are x updates to data in the supertype table, creating x supertype history records, and y updates to a subtype table creating y subtype history records. If the changes are made on the same day, how would I reconstruct records?
Is this a good use of supertype/subtype, or should I denormalize the tables?
Otherwise, can anyone suggest any way to approach the history issue for this type of design?
Using MS SQL Server 2008.
Here is a very simplified ERD:
Best Answer
It sounds like what you want is an implementation of transaction-time temporal tables. It's harder than it sounds.
Reference: Developing Time-Oriented Database Applications in SQL, by Richard Snodgrass, p 20.
This book was the only book I could lay my hands on when I was working on this stuff some years ago. Now you can download a PDF of the book and the accompanying code from the author's web page.
Newer material might be more useful. There are links on his web page.