Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
3  
contract_type_history might describe it better. –  Henrik Ripa Jul 13 '12 at 14:01
1  
Warning! Conceptually, to_date is derived information - assuming that a contract must have a type, it will have a given type until the instant before the new type is chosen. A completely normalized database would remove the column, and use from_date of the next type if it needed to derive the range. Performance considerations, of course, may require the storage of the ending column. As always, store ranges as lower-bound inclusive, upper-bound exclusive. Also, it's usually better to not suffix type information to column names - use something like 'changedOn' instead. –  Clockwork-Muse Jul 13 '12 at 17:25

3 Answers 3

up vote 4 down vote accepted

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.

enter image description here

share|improve this answer

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.

share|improve this answer
    
+1, valid alternative indeed. –  Emmad Kareem Jul 13 '12 at 16:50

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.