History tables for many to many relationships

3
votes

I need to track the version history of several entities, to be able to see the state of all its properties at any given point in its history, that includes their many to many relationships. It also has to support changesets. Given the following tables:

EntityA
-------
Id
Name
Revision
ChangeId

EntityB
-------
Id
Name
Revision
ChangeId

EntityAToEntityBMapping
-----------------------
EntityAId
EntityBId
ChangeId

ChangeTracking
--------------
ChangeId
Date
User
Description

For EntityA and EntityB tables I would have a history table with the exact same columns. The data is always modified through an Object Model, so before any change is committed I would create a new ChangeTracking entry and add its Id to any entities being inserted or updated, plus incrementing the Revision. I then have an Insert/Update trigger on EntityA and EntityB tables, and would copy the previous data into the history table. At any given point you have all the history for an entity in its history table.

The problem comes with many to many mapping tables. I could do the same for the mapping table and have a history table, but I need to be able to say "at revision 3, this EntityA had these values for their columns, and these relationships to EntityB". EntityA history table could be joined with the mapping history table by ChangeId, but what happens when only the name of the EntityA is changed? At that point the relationship history would be lost, since it's a new revision.

I can think of three scenarios that need to be supported:

  1. Only EntityA is changed, no changes in relationships.
  2. Only relationships between EntityA and EntityB change.
  3. Both EntityA is changed and its relationships modified.

For all these, consistency needs to be maintained and I should always be able to get all properties and all relationships for a given revision of EntityA.

Do you know how I can handle history for many to many tables? After searching a lot I haven't been able to find a satisfactory solution to this problem. By the way, I'm using Entity Framework and SQL Server in case its relevant.

Thanks

1
sql-serverdatabaseentity-frameworkdatabase-designmany-to-many

1 Answers

0
votes

This is somewhat different from what you might in your head, and it is more simple, what may not be a problem, if it supports all your requirement scenarios. I hope I did not miss any of them.

Case #1 Only EntityA is changed, no changes in relationships.

  • A copy of EntityA is inserted into EntityAHistory (with the actual date)
  • A copy of EntityAToEntityBMapping is inserted into EntityAToEntityBMappingAHistory (with the actual date)
  • EntityA is changed and gets a new revision

Case #2 Only relationships between EntityA and EntityB change.

  • A copy of EntityAToEntityBMapping is inserted into EntityAToEntityBMappingAHistory (with the actual date)
  • EntityAToEntityBMapping is changed and gets a new revision

Case #3 Both EntityA is changed and its relationships modified.

  • A copy of EntityA is inserted into EntityAHistory (with the actual date)
  • A copy of EntityAToEntityBMapping is inserted into EntityAToEntityBMappingAHistory (with the actual date)
  • EntityA is changed and gets a new revision
  • EntityAToEntityBMapping is changed and gets a new revision

(actually case #1 and case #2 can be combined, it is just redundant to copy EntityAToEntityBMapping into EntityAToEntityBMappingAHistory twice)

When you join any of EntityAHistory, EntityBHistory, EntityAToEntityBMappingHistory
you'll want to include not only the Id but also the Revision field in the join. E.g. you want to see the history of a certain revision of EntityA:

select * 
from EntityAHistory A, EntityBHistory B, EntityAToEntityBMappingHistory M
where A.Id = EntityAToEntityBMappingHistory.EntityAId
and B.Id = EntityAToEntityBMappingHistory.EntityBId
and A.Revision = EntityAToEntityBMappingHistory.EntityARevision
and B.Revision = EntityAToEntityBMappingHistory.EntityBRevision
and A.Revision = :CertainRevisionOfEntityA

The model:

EntityA
-------
Id
Name
Revision

EntityB
-------
Id
Name
Revision

EntityAToEntityBMapping
-----------------------
EntityAId
EntityBId

EntityAHistory
-------
Id
Name
Revision
Date

EntityBHistory
-------
Id
Name
Revision
Date

EntityAToEntityBMappingHistory
-----------------------
EntityAId
EntityARevision
EntityBId
EntityBRevision
Date