We've lately been favouring a different scheme for tables-with-history with some advantages and disadvantages.
*Basic Idea:*
- Have one table A (say "contacts") with the usual auto-inc primary ID (RowID alias in the case of SQLite), or any other defined primary key,
- then a second table H of the form "history_xxx" ("history_contacts" for example) with it's own row_id or Primary Key
- and one extra field for the original row-id/PK in A (but non-Unique in H).
- The rest of the schema matches exactly table A.
An ON UPDATE Trigger on table A writes, whatever the current (before-update) row values (OLD.xxx) are, to the H table.
So at any point in time we have Table A with the current-most values, and Table H with the values as it were at that point in time.
*Advantages:*
- Table A doesn't grow with loads of history, remains fast.
- Table A doesn't need a combo-PK to account for Time - the base PK works just fine and doesn't change (especially useful for FK relations).
- Table H can be joined to Table A to see what changed at any point till now.
- We have automated code that can build the History table from any given table, and to update a History table schema, which makes things easier than having to plan how to use a single table with history.
- We can thus run this automated code and "historify" even any old project in a jiffy where we didn't have history before, because we do not touch the original tables (excepting adding the ON UPDATE trigger).
*Disadvantages:*
- There are now 2 tables and not just 1 for every table where history is required.
- An update to table A's schema has to always be accompanied with an update to H's schema - even if we have an automated way of doing it, it requires at least some testing. (Depending on how often this happens, this is a strong consideration).
*Note:*
Our tables ALWAYS contain "UpdateTime" fields which then works as-is in the history table and defines the time up-to-which the data in that row persisted in the live table A, but if you don't have that, the History table will need some timestamp column added to do that job.
Hope that offers another option in your quest.
Cheers,
Ryan