Apologies if this isn't a clear question. Let me give you an example.
A customer wants a database that will allow him to store information about each project that he creates for each of his clients. So Client A hires him to paint 5 houses, and the customer creates a PaintProject for each, with the data needed to figure out the cost per square foot. Then he can create a budget that can be sent to the client. However, sometimes the client will come back and say, "well, what if we don't use THAT paint, but use a cheaper/more costly paint?" So the customer has to edit some of the information, which is then used to create an updated budget sheet for the client. This may happen multiple times.
So what is the "best" way to keep track of the various modifications over time? The customer does not want to only keep the most recent changes, and would rather not have to sift through printed copies of these budget sheets that are sent out.
I had two rough ideas... one way is to let the customer decide when to create a new version. He's have a button in the front-end that would be labeled with "Create New Version", and when clicked, the current data would be copied over to a table (or group of tables) that exist for the sole purpose of tracking versions to these budget values.
The other idea would be to automatically do something like the above system... no user intervention required. This would undoubtedly lead to many more versions, however, because the customer will often "play with" the numbers to see how the budget total is affected. (A customer may not be sure if he wants to have his detached garage painted or not, or may or may not want to have a special type of primer used, etc.).
Anyway, am I sort of on the right track with thinking that I should use a separate set of tables for the versions of these fields/records? Or is it better to try to track everything in the same set of tables, using additional field(s) to identify versions?
A customer wants a database that will allow him to store information about each project that he creates for each of his clients. So Client A hires him to paint 5 houses, and the customer creates a PaintProject for each, with the data needed to figure out the cost per square foot. Then he can create a budget that can be sent to the client. However, sometimes the client will come back and say, "well, what if we don't use THAT paint, but use a cheaper/more costly paint?" So the customer has to edit some of the information, which is then used to create an updated budget sheet for the client. This may happen multiple times.
So what is the "best" way to keep track of the various modifications over time? The customer does not want to only keep the most recent changes, and would rather not have to sift through printed copies of these budget sheets that are sent out.
I had two rough ideas... one way is to let the customer decide when to create a new version. He's have a button in the front-end that would be labeled with "Create New Version", and when clicked, the current data would be copied over to a table (or group of tables) that exist for the sole purpose of tracking versions to these budget values.
The other idea would be to automatically do something like the above system... no user intervention required. This would undoubtedly lead to many more versions, however, because the customer will often "play with" the numbers to see how the budget total is affected. (A customer may not be sure if he wants to have his detached garage painted or not, or may or may not want to have a special type of primer used, etc.).
Anyway, am I sort of on the right track with thinking that I should use a separate set of tables for the versions of these fields/records? Or is it better to try to track everything in the same set of tables, using additional field(s) to identify versions?
