Best Practice for Versioned Entities?

Question

Good afternoon,

I am currently in the very early phase of a new project written in .Net and using Entity Framework for data persistence/storage. One of the features required is the ability to 'version' certain model types. E.g. one model is one 'Requirement' which will have n 'Requirement Versions', basically having a way of going back in the history/lifecycle of that particular 'Requirement' instance. The only thing that has to be static across all revisions is it's 'ID', everything else is absolutely changeable throughout the requirement's lifetime.

Now the Q is, should I 'simply' create a 1:n relationship between a Requirement >> RequirementVersion? Other features need are the possibility to completely revive old states to be the current/latest one, there must be the ability to have minor and major version (changes) and such, and last but not least the ability to create a 'Baseline' across a collection of Requirements with the latest version to go back to that particular Baseline at a later point of time and displaying all of the included RequirementVersions?

This has to scale up to a couple million Requirement Records, each of them having a couple thousand revisions.. that's particularly why I am asking.. the scaling aspect of a simple 1:n relationship etc.

Has anyone done something similiar and maybe some suggestions/best practices etc regarding versioning/baselining etc?

Cheers & thanks, -Jörg

Answer 1

This depends on how much data each Requirement has.

If Requirement has large fields (e.g. Requirement Description).

1. You probably would like to version fields instead, not Requirement itself. Unfortunately, seems there is no easy way to deal with Entity Framework.
2. Other solution (we did this for CMS) is to have separate tables for Requirement and for Requirement Description. So as result you will have RequirementVersions and RequirmentDescriptionVersions.

If Requirement is small enough you can use Requirement >> RequirementVersion. In most cases you will not have significant data grow, especially you can utilize SQL2008 compression.