Model Design Patterns For: Document Management System

The following design pattern I’ve known for several years and have implemented it to great success. The subtles are what will save you:
1. Having an explicit ExpirationDate will save you having to look up what an initial contract’s end date is when you imply that the Start date of the replacing contract is the end of initial contract (When in doubt, be explicit not implicit);
2. You can use Between statements for getting contracts that are currently active;
3. Not mixing non-customer-visible change history with official versioned data will save you from having to always filter change history out of the resultset.

The design pattern below will cover a simplified contract management model that will track the work flow status of a contract and provide simple change control. This example is just to demonstrate the design pattern and can be used in variation for other concepts as well. It will simplify the understanding of your system.

Concept: Contract
A contract defines the terms for services to be rendered in exchange for money between two or more legal entities. A contract can not be changed once it has been activated. If you require to change the contract after the contract is activated you need to Terminate the contract and create a new contract with the required changes.

Concept: Contract Effective Date
A contract’s effective date represents the date the contract will be activated. This date can’t be changed once the current date is less than its effective date.

Concept: Contract Expiration Date
A contract’s expiration date represents the date the contract expires. Open ended contracts have no expiration date.

Concept: Contract Termination Date
A contract’s termination date represents the date the contract was terminated. Use this date to prematurely cancel a contract and yet preserve the original expiration date.

Concept: Contract Status
A contract’s work flow is controlled by its status: Draft, Approved, Active, Terminated, Expired.
1. Draft – Represents that the contract is a draft. Contract and terms can be changed freely.
2. Approved– Represents that the contract has been approved. Contract and terms can NOT change except the Contract Status, Effective Date and Expiration Date.
3. Active – Represents that the contract is activated. Contract and terms can NOT change except the Contract Status and Expiration Date.
4. Terminated – Represents that the contract has been terminated prematurely. Contract and terms can NOT be changed.
5. Expired – Represents that the contract has naturally Expired based on the contract’s expiration date. Contract and terms can NOT be changed.

Concept: Change history tracking
Change history tracking tracks all changes done to a table. Please see http://dataglass.blogspot.com/2006/03/how-to-build-change-log-into-your.html for technique.

Contract
------------------
ContractID (PK)
ContractNumber (AK)
… (Other attributes about the contract)
…
…
EffectiveDate
ExpirationDate
TerminationDate
StatusCode
CreatedBy
CreatedDateTime
ModifiedBy
ModifiedDateTime


ContractHistory
-----------------------
ContractID, RecordedDateTime (PK)
ContractNumber
… (Other attributes about the contract)
…
…
EffectiveDate
ExpirationDate
TerminationDate
StatusCode
CreatedBy
CreatedDateTime
ModifiedBy
ModifiedDateTime
ActionType

Status
----------
StatusCode (PK)
StatusName (AK)
StatusDesc
CreatedBy
CreatedDateTime
ModifiedBy
ModifiedDateTime