Design to accomodate entity based schema versioning

in simple words it's about versioning at record level.

Example

TableEmployee - EmployeeId, EmployeeName,
EmployeeAddress, DepartmentId,
TableDesignationMap - EmployeeId, DesignationId, EffectiveDate,
validity
TableDepartment - DepartmentId, Department
TableDesignation - DesignationId, designation

Via Modify-Employee-Details screen following are editable
EmoyeeName
EmployeeAddress
Department
Designation

this screen should allow user to navigate through changes history.
Example :

Version -1
EmoyeeName John Smith
EmployeeAddress 60 NewYork
Department Accounts
Designation Accountant

Version -2
EmoyeeName John Smith
EmployeeAddress 60 NewYork
Department Accounts
Designation Chief Accountant - changed

Version -3
EmoyeeName John Smith
EmployeeAddress 60 NewYork
Department Sales - changed
Designation Marketing Manager - changed

Question :
What is the best proposed database design for maintaining history
records bound with version and retrieval technique

Best Regards
SasankaSasanka Pinidiya (pinidiya@.yahoo.com) writes:
> in simple words it's about versioning at record level.
> Example
> TableEmployee - EmployeeId, EmployeeName,
> EmployeeAddress, DepartmentId,
> TableDesignationMap - EmployeeId, DesignationId, EffectiveDate,
> validity
> TableDepartment - DepartmentId, Department
> TableDesignation - DesignationId, designation
> Via Modify-Employee-Details screen following are editable
> EmoyeeName
> EmployeeAddress
> Department
> Designation
>...
> Question :
> What is the best proposed database design for maintaining history
> records bound with version and retrieval technique

There are several techniques, and which is the best depends on the actual
business case.

One techniques which makes it very simple to retrieve the data at a
certain date, is store one version each day. Clearly, this technique
is not appropriate here. This technique is good for things that changes
about daily as for instance currency prices.

Another technique is store key, date and then what changed. But this
makes is difficult to retrieve the complete information at a given point
in time.

So the best technique seems to be to have a table with the same columns
as the base table, and with a datetime column added to hold the time
of the change.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp