I am unsure if it question continues to be clarified before. During my database I've got a product table and specifications table. Each product might have multiple specifications. Here I have to keep revisions of every product in database to be able to query them afterwards for history reasons.
And So I need a competent method to keep products' relations to specifications every time customers make changes to those relations. Also the quantity of data may become very large. For instance, suppose you will find 100000 items in database: each product might have 30 specifications as well as you will find the least 20 revisions on each product. So by storing all of the data in one table the quantity of data becomes enormously high.
I would suggest getting a table, exact copy of current table having a HistoryDate column, and keep revisions within this table. This that you can do for those 3 tables under consideration.
By continuing to keep the revision outside of the primary tables, you won't incur any performance penalties when querying the primary tables.
You may also take a look at keeping an archive to point the consumer that transformed the information.
If this sounds like purely for 'archival' reasons maybe another table for that revisions is much better.
However if you want to treat previous revisions equally to current revisions (for instance, if you wish to give customers a chance to revert an item to some previous revision), then its most likely better to have a single items table, instead of copying data between tables. If you're concerned about performance, this is exactly what indexes are for.
You may create a substance primary key for that product table, e.g.
PRIMARY KEY (product_id, revision). Perhaps a saved proc to obtain the current revision&mdashby choosing the row using the greatest
revision for the
product_id&mdashwill be helpful.
See a very similar question by having an answer.