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.

StackExchange Database Administrators Q#33520, answer score: 5