Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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..)

share|improve this question
add comment

2 Answers

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.

share|improve this answer
add comment

Your question covers many different topics, so it might be better to split it into a few.

You seem to be trying to combine many things at once, perhaps for the sake of simplicity, but it seems to be making it more complex.

Write out your requirements. Turn the nouns into tables and the adjectives into junction tables.

http://blogs.perl.org/users/ovid/2013/07/how-to-fake-database-design.html

You have Geopolitical Areas to which laws apply, such as say a tax rate. A Country, State, County, City are examples.

You also have Governmental Organizations, which make laws.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.