Storing Revisions of Relational Objects in an Efficient Way

Question

I'm not sure if this type of question has been answered before. In my database I have a product table and specifications table. Each product can have multiple specifications. Here I need to store the revisions of each product in database in order to query them later on for history purposes.

So I need an efficient way to store the products' relations to specifications each time users make changes to these relations. Also the amount of data can become very big. For example, suppose there are 100000 products in database: each product can have 30 specifications and also there are minimum of 20 revisions on each product. So by storing all the data in a single table the amount of data becomes enormously high.

Any suggestions?

Answer 1

I would recommend having a table, exact copy of current table with a HistoryDate column, and store the revisions in this table. This you can do for all 3 tables in question.

By keeping the revision separate from the main tables, you will not incur any performance penalties when querying the main tables.

You can also look at keeping a record to indicate the user that changed the data.

Answer 2

See a very similar question with an answer.