Database Design: how to track history?

Question

What are the general strategies in DB design to maintain a revision history? If it were just one table I was dealing with, I think it wouldn't be so hard. Just save each update as a new record in the table. The last record will always be the latest revision.

But when the data is stored across multiple tables, what's a good way to design that so that it can track revisions?

Answer 1

I prefer to have additional historical table for each versioned table. Same structure as main table with time_from and time_to additional fields. Transparently filled with triggers. time_to of latest revision set to far far future.

State for specified moment can be retrieved with query like this:

SELECT * FROM user_history 
WHERE time_from >= '2012-02-01' AND time_to <= '2012-02-01' 

As for me, storing history within main table is not generally a good idea, as it requires complicated conditions when retrieving or joining current data.