Hi,
My current schema in my application uses record versioning. Every element of the system may have multiple versions associated with it's element ID. The reason for this schema is
because the DB will be time conscious... If i re-open a quote from 5 months ago, I would like to see it exactly as it was 5 months ago...
When an element is being created for the first time, its version is set to 1. When an element is being updated, a new record is inserted into the table with the max version plus 1.
Problems with this Schema:
*note: this is an entirely unbound application
1.) The growth rate of the system just exponentially increased.
2.) The development time has increased greatly (must always query for max version to get latest data)
3.) The cost to query mass amount of records simply to retrieve the latest data can great.
4.) Where one thing is versioned, everything must be versioned... A versioned schema only makes sense when almost every orphan is versioned. This goes back to exponential growth.
The only two solutions I can think of are:
1.) Manage redundant saves (do not write new version if no changes are present)
2.) Create a History Table for each master table. When a new version is created, transfer previous to history table - this way when needing to query all the current versions, there's far less data.
3.) Re-think about how and when versions are needed. For example:
The current methodology is: *note: put in forum quotes only for formatting purpose
A possible alternative is:Lets say I have a quote. I add a customer ID to the quote. This pulls all of the customer info into the quote.
When the quote is saved (which is also versioned) it saves the customer ID and version of that customer ID.
Reopening the quote 5 months from now will load the same customer data that existed 5 months ago even if the latest customer version changed. -- good (will warn if out of date)
When a user is modifying/viewing a Customer ID in the Customers module and they click save, this does NOT create a new version. It modifies the current version.
However.... when a users adds a Customer ID to the quote, that version is remember by the quote but also a NEW version is created in the customer table.
So, the process repeats.
The difference between these two methods are that versions are created on the fly only when needed versus creating a new version every single time a customerID is saved.
I have not entirely thought this second approach through yet. It may be less efficient in the long run.
Ultimately, I am inquiring on the best approach to design this schema. The scope is not massive for this project, but the data could be if I do not be careful.
Thanks for your help.
Regards