Date Effectivity Requirements

Many software requires tracking the effectivity of the data over periods of time. I have run into it several times in the application design. It makes sense to establish the design patterns to address the software data model / architecture design.

Here are the common requirements I have seen:

1. You want to allow the user to enter the data ahead of time to the system, but the data should become effective until a given date. You need to be able to enter a future effective start date for your data.
2. You want to allow the user to change the data ahead of time. When the effectivity date for the new record is reached, the new data comes into effect and the previous one is abandoned.
3. The data entered in the past may be still effective in the past periods of time. It is possible that when the user references the data for a past point of time, only the data effective at that past point should be allowed.
4. The data with different effective dates are considered as different "versions" of the data for the same object so the object identifier need not be changed over the period of time. At least a "user key" can identify the records which belongs to the same object and the periods of these records should not overlap.
5. You may want to allow the users to update the data in the past periods. This change may be involved in changing the effective date when the past data become effective. The effective date may become simply earlier or later, as long as they won't overlap with the other periods.
6. The change may also involve in adding new record with a new effective date between two existing records. The record that has the latest effective date of the new record is considered becoming ineffective earlier. The new record will only be effective before the next successive period.
7. Sometime deleting a record may be allowed so the data from the immediate prior record become effective in the period of the deleted record.
8. Sometime cascading the history data correction from a (past) effective date over multiple future periods/versions is necessary.
9. You should be able to query the data for any given point in time. The record effective at the time point should be retrieved. The problem becomes even more complicated since all the master child relationships should be correct.
10. The child records of a master table may be specific to a period of the master. The child data won't be effective until the master record is effective. If the master record is superseded by another record, another set of child records should be created.
11. The effectivity of the reference data need to be consistent with the transactions that reference it. The transaction date should be used to retrieve the correct version of the data.

There are related requirements about the activeness of an object. It should not be confused with the effectiveness of the records or versions of an object.

There are also requirements about auditing and tracking history of the update. The requirement can be different or just a s subset of the above requirements.