Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

ERD

share|improve this question
add comment

2 Answers

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.

share|improve this answer
    
Thank you for that reference. Looks like a great book. –  TheSecretSquad Feb 15 '13 at 0:06
add comment

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.

Device_Phone_History
-SerialNo
-DeviceNo
-etc...
-PhoneNumber
-UpdatedBy
-UpdateTime

I hope my idea is of some use to your problem

share|improve this answer
    
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. –  TheSecretSquad Feb 15 '13 at 0:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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