Part of one of my applications has contracts and contract_types tables, wherein a type may have many contracts but a contract may only be of one type. Now a new wrinkle has been introduced: a contract may change type over time (although it can, thankfully, still only be of one type at any one time).
The simplest solution appears to be to introduce a new table with columns something like this:
contract_id
contract_type_id
from_date
to_date
But what to call the new table? contract_type_allocations is the best I can do so far and I can’t say I’m impressed with myself. Suggestions gratefully received.
2
You have 2 options as shown below. In option2, the 2 FKs make up the PK of the history table.
Note that if you have a case like this:
Contract 1 – Type A
then it changes to:
Contract 1 – Type B
The above can be taken care of by the solutions provided. But if you add an extra change as:
Contract 1 – Type A (back to type A again), the 2nd solution fails! – To avoid this, you can create a primary key on the history table that is GUID or sequential number and you don’t impose uniqueness on the combination of the 2 FKs.
Another way is to set up views. Use the latest versions in the view so joins with the view can stay 1 to many whereas the base tables are many to many. The many to many can be used for history if required (this then depends if you want/need to maintain the many to many/use a linking table to give 2 way 1 to many/introduce version in the key to make unique/etc – depending on the use you make of it and what will join with it if anything).
Another way to go, is to have a history table and move relevent rows over when contract type is changed. This then allows for the same visibility as the view mentioned above, but allows for more accurate indexing (accurate in so far as it matches your operational use of the table) – the history table will of course become many to many too (after any second change). This can be more efficient (especially if there are many rows on the table), but requires more housekeeping/pre-processing.
0
This is a similar challenge faced when creating immutable objects.
Adding versions to the contract and / or creating a history table are the two quickest solutions.
A version column would allow you to leave the older versions of the contract information in the same table, which may be advantageous. It could also present problems by adding bloat if the table is already large. This would also allow you to track other significant changes within the contract itself.
Otherwise, it’s simple enough to setup an additional table as you suggest. “History” or “Version” would be good alternate table names.