Database design for changing data

Question

I'm trying to design a database to store information about an entity. This entity will pass through 5-6 stages. In every stage may happen some property modifications. It is necessary to have access to the state of the entity in every stage. Also each stage will have some additional properties unique for the current stage.

I'm wondering what would be the best way to design the tables. I was thinking to have a separate table for each stage (but the tables would have the same structure as the structure remains the same through all stages). Also for each stage table I would be able to create relations tables.

The only thing that is bugging me about this design is that I will have 5-6 tables with the same structure. On the other hand if I would put all the stages in one table and have a column which determinates the stage, then I don't see a way to create relations table per stage.

Any thougts ?

Answer 1

There are generally 2 approaches:

• Store all in one table, with a timestamp / version number.
• Store the current version in one table, with an archive table for the older versions.

The first makes sense in a data warehouse mostly, while the second approach allows applications to be easier (as long as they do not care about the historical data). The archive table can be automated via triggers.

Having one table per state is the worst of post worlds - it makes application development horrendous, particularly as you have to rework all applications when you add an interim state. It also makes referential integrity painful.