Sql-server – Record versioning and promotion

database-designsql serversql-server-2008-r2

Let's say we have this hierarchy: 

   -World  
        --USA
        ---WA
        ----Seattle
        -----Downtown
        ------1st Ave
        -------945 1st ave
        ------3rd Ave
        -----South
        ----Bellevue  
        ---TX  
        ---MI  
        --Canada   
        ---BC

Now, this will not reflect real life completely, but.

  1. At location World we have some global settings. Let's say: list of Races, list of Languages, etc. All locations underneath it can access this data.

  2. Country level holds data about laws, currency, etc.

3.Each city can modify laws. After they modified the law, it is available to the city itself and to the regions within the city. After 3-step approval process, it can become a country level law, and all children will inherit new version of the law. Each city still can modify new version of a law to have changes.

  1. Every City has sandboxes. This means that different organizations within city can test out editing different versions of laws without affecting City master revision.

Currently we solve this issue like this:

We have table Location with HierarchyId
Then we have table Law that has columns StateType, Location_Id, etc.
StateType can be: Master (Available to all within country), Edited (Edited for a specific location), Approved (Approved at a specific location).

Because of this we have to use Table-valued functions in our application to filter specific records that are relevant to the current location. (Some laws can come from country level, some laws come from city level, some laws come from organization level). This makes database much slower.

We cannot use native unique constraints on our fields, because all fields must be unique only within a branch, so we have to use custom check constraints to keep data clean.

Also, insertion and editing records is much slower, since we have to promote some records to a country level, and then apply data transformation for other countries.

Currently we have around 150 tables, and we use a lot of joins.

I'm not sure what's the best way to model complex hierarchical data with versioning. (I'm thinking we need an enchanced version of source control..)

Best Answer

Use one of the various techniques for modeling hierarchical data in a relational database. My preferred approach is visitation numbers (see my answer to this question for more information about visitation numbers) but you could also use adjacency lists.

This will simplify the retrieval of Laws that pertain to a particular Location.

You will also want to include some kind of level indicator on your locations. This will allow local overrides of broader laws to be identified. You can sort by the level indicator to pick the most localalized version of any particular law.

Related Question