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: