How to allow incremental versions of the data in a DB
Hi, I´m designing an application to register legal process. There are two main concepts: the Legal Process and the Survey. Each Survey will have a set of Process. There always will be an "active" Survey, and each new Process created will be associated to that one. The system must allow the user to generate reports with the information of an old Survey (diferent than the actual one), and compare it with a report generated from the active Survey.
Also, the administrator will have an option to close the active Survey and open another one.
Because of the nature of this kind of data, the information can always change. But the system must to keep the information related to an old Survey.
Basically, the users must think that closing a Survey creates a copy of all the information that can't be modified, and then all his changes will be done in a separate copy. So later he will be able to generate reports from the old information.
The first idea is to have a table Survey, and a foreign key to that in a table called LegalProcess. Then, when closing the Survey, the system literally copies all the data, but inserts the new rows in LegalProcess changing the Survey ID.
But, there can be much information that won´t change any more. And this option would increment exponentially the amount of data on disk.
My idea is to create, for each attribute in LegalProcess, a table that contain its value, the Survey ID and the Process ID. So, if that value never changes, there will be no unnecessary copies. For example, the LegalProcess have a File number. So, there will be a table called LegalProcess_FileNumber, with the attributes:
autoID INT AUTOINCREMENT (to simplify selects and updates)
FileNumber VARCHAR
LegalProcess_ID INT
Survey_ID INT
So, what do you think about that? Any idea?
Thank you!