History table design for supertype/subtype 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:
It sounds like what you want is an implementation of transaction-time temporal tables. It's harder than it sounds.


  Valid time, capturing the history of a changing reality, and
  transaction time, capturing the sequence of states of a changing
  table, are orthogonal, and can thus be separately utilized or applied
  in concert. A table supporting both is termed a "bitemporal table".


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.