How to create relationship between two tables with revisions using Entity Framework

Question

So I am in the process of redesigning a small database (and potentially a much larger one) but want to show the value of using revisions / history of the business objects. I am switching the data from Access to MSSQL 2008.

I am having a lot of internal debate on what version of "revision history" to use in the design itself - and thought I had decided to add a "RevisionId" to all tables.

With this design - adding a RevisionId to all tables we would like tracked - what would be the best way to create Navigational Properties and Relationships between two tables such as

| Vendor | VendorContact |  

where a Vendor can have multiple contacts. The Contacts themselves will be under revision. Will it require custom extensions or am I over thinking this?

Thanks in advance.

Answer 1

So presumably your key for the Contact table would now be a (generated unique ID + a Revision Id).

And there will be a FK relationship between Vendor and Contact using just the unique ID. So it will be a 1:many mapping.

And a simple request to get the current contact will become vendor.Contacts.OrderByDescending(c => c.RevisionId).First() or you can get the entire revision history for that contact if you want.

OR are you trying to track how that contact has changed over time (i.e. it was person A and not it's person B)? i.e. does the relationship itself need to have a RevisionId on it?

Or are you perhaps trying to track both revisions to the relationship AND revisions to the contact it points to?

vendor.VendorContacts.OrderBy...().First().Contact.OrderBy...().First()

This could get ugly pretty fast!