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:


2 Answers 2


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.


I would personally go for the historic table approach with the supertype/subtype design because it will allow you to track the changes to each table independently while still being able to efficiently query the latest data from the non-historic table. But keep in mind to update the main tables with the latest info, i.e. update Printer with the values to be inserted in PrinterHistory.

Use datetime instead of date alone so that you can accommodate for changes done on the same day, and instead of joining the historical tables, just view them separately to avoid conflict of joining them in the case that the supertype is updated independently from the subtype.

But if splitting the hisotry table of the supertype and the subtype is not possible, then create a slightly denormalized history table for each device type. i.e.


I hope my idea is of some use to your problem

  • I think I might try to incorporate this in some form as it appears to be a simple and effective solution. Thanks for the suggestion. Feb 15, 2013 at 0:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.