Location equivalency headaches

Bryan and I came up with this while trying solve migration and location/address integrity issues.

To define how multiple Location subordinates are equivalent, consider the following. The Location (country) USA has "states", "commonwealths", and a "district". It's neighbor to the north, Canada, has "provinces" and "territories". Likewise, the UK has "counties" or "post towns". On a formal level, these could all be called, "level 1 subordinates". If you have a Location table that defines a location by its type (and an id), then you could have the following:

Location
---------
id  type    name
--  ----    ----
1   country USA
2   country Canada
3   country UK
4   level1  Alabama
5   level1  Manitoba
6   level1  Southhampton
7   level1  Puerto Rico
8   level1  District of Columbia
9   country Mexico

LocationType
------------
id  type
--  ----
1   country
2   level1
3   level2
4   level3
5   level4

Note, "level*" indicates a level of subordination. Using a LocationAssociation table you can build a hierarchy of locations and their subordinates.


For address creation, how could you equate like levels and provide an appropriate naming scheme in the context of the country? Here's one possible set of tables providing a solution:

First is a Subordinate Types table that defines the types you will use to identify subordinates (any level). This table could have a translatable counterpart that provided localized identifiers (I18n) per row, thus enabling translations for a single word, in multiple languages. (This is beneficial in the following model because it constrains translation to a single word and the context is derived based on equivalency relation.)

Subordinate Types
-----------------
id  type
--  ----
1   state
2   province
3   territory
4   county
5   district
6   commonwealth

The Subordinate Equivalence table will relate a location, a locationType, specify if the row is a default (more on this in a moment), subordinateType, and a pointer to a localized string used to identify that component in the native country (locationId). This table realizes the equivalent nature of LocationTypes and SubordinateTypes in context of the owning location (usually country).

Subordinate Equivalence
-----------------------
id  locationId  locationType   defaultBit  subordinateType   defaultLocalizedStringId
--  ----------  ------------   ----------  ---------------   ------------------------
1   1           level1          1           state              ->"State"
2   1           level1          0           district           ->"District"
3   1           level1          0           commonwealth       ->"Commonwealth"
4   2           level1          1           province           ->"Province"
5   2           level1          0           territory          ->"Territory"
6   3           level1          1           county             ->"County"
7   3           level1          0           posttown           ->"Post Town"
8   1           level2          1           county             ->"County"
9   1           level3          1           city               ->"City"
10  2           level3          1           city               ->"City"
11  3           level4          1           city               ->"City"
12  9           level1          1           state              ->"Estado"
13  9           level3          1           city               ->"Ciudad"

Thus, this table relates that "level3" is common across USA, Canada, UK, and Mexico. But, "level2" has no associated alternatives *in those countries*. Likewise, the word "county" is used twice, once to show a "county" is "level2" locationType in the USA. BUT, it also shows that it is a "level1" for the UK. So, the UK "county" could be considered equivalent to a USA "state" and Canadian "province" and "territory". This is quite helpful to know in address construction and UI presentation. The "defaultBit" shows which is the default subordinate type for that level, to display to the user.

I18n Benefits:

With the use of the FK to the Subordinate Types table, the i18n value of the subordinate type can be easily fetched. This is most helpful for countries that support multiple languages (ie. Canada or Switzerland).

Likewise, if a user in the USA wants to know what an "Estado" is, we can determine the equivalent through this table and provide an appropriate translation.