I have several of the following situations in a MySQL schema that I design for a custom CRM/ERP solution.
1:N related tables
SO_ITEMS(SO is just one example I have several) that both need to have revisions. The data sets stored tend to be get pretty big for some of these tables and, according to the business rules I gathered, there will be many changes that need auditing so copying all the child items for each revision of the parent will not scale very well I predict.
N:N related tables
POtables (where I use a pivot table of course) that needs to be audited also so that changes from at least one way imply (or may imply) storing an updated revision of the data entry form the other table. (We need no worry about the child
_itemsdata entries for each of these tables because although they may be related, I don't require auditing for now).
My strategy for now is to use a separate table suffixed with
_revision for each of the above tables and store the current revision number in the normal table and a revision number for each revision, at least for the parents.
So my question are:
- is my schema flawed in some major way that I don't predict for this kind of scenario? (I suppose there are many other applications that have solved this problem out there).
- should I make a direct relationship between
y_revisionor use the revision number and query through the normal (current revision?) table? I want the variant that reduces complexity the most and I'm pretty sure querying through is simpler on most operations (I suspect there will not be a big disparity between SELECT/INSERT/UPDATE in normal operation, so I don't need to optimise for one type of DB operation). Am I missing something?
- is it common practice to copy-on-write the revisions or should I just lazily insert entries in the revisions table when a change appears only? Any possible pros/cons to these two options.