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.
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.
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.
- 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
Then we have table Law that has columns
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..)