I am being entrusted with the redesign of a database architecture that contains hundreds of Entities and the Transactions involving these Entities. There are many aspects of the current architecture which need to be addressed; however in an attempt to minimize the scope of this post, the requirement I am concerned with here is that the database must preserve the State of Transaction Participants (Entities) at the point Transactions occur so that historical reporting remains accurate.

The current strategy is to copy all the pertinent State information into the Transactions, so that if the Entities change (either modified or deleted), the Transactions are left unaffected. As you can imagine, some of these Entities carry a lot of state, which although may change, probably rarely changes, resulting in a large amount of unnecessary duplication of data in the Transaction tables.

To illustrate the problem, consider a very rudimentary example representing an Employee, and a record of weekly paychecks.



Here the state information consisting of the Employee’s LastName and FirstName is maintained in an Employee table; however because there is a chance the LastName could change (for example, in the event of a marriage), the name information is copied into each weekly paycheck record to assure accurate reporting down the road.

I am by no means a DBA with formal training in database design; and although I have 30+ years of development experience involving databases, I am posting here with hopes that the community can either validate or invalidate the following approach I am considering.

To avoid the unnecessary duplication found in the current database, I have moved the State information from the Employee table into an EmployeeState table and added a link between the EmployeeState table’s primary key, EmployeeStateID and the Employee table’s CurrentEmployeeStateID.



When the Employee’s State changes a new EmployeeState record is created, preserving the old State record. The Employee record’s CurrentEmployeeStateID field is updated to reference the new EmployeeState record.

When a paycheck is issued, the EmployeeStateID of the Payroll record is initialized from the Employee record’s CurrentEmployeeStateID field pointing to the EmployeeState record valid at that time. Since that EmployeeState record will never change and is preserved, anytime the Payroll record is accessed, the correct State of the Employee will be referenced.

Any opinions on this approach or alternatives would be greatly appreciated.

Thanks in advance,
Steve