design DB model for addresses & territories

[christopheprudent]

idea is to store elements of address (street, housenumber, ...) as dictionnary (uniqness) and next to build an entity for address, which have (or not) each element

an address :

• belongs to a territory : this territory is itself linked to parents as district, zipcode, department, ...
• has zero or one parent
• can be associated to multiple XY (as INSPIRE defined: entrance, postal, ...)
• is associated to one or more referential (with an ID for each: CEA for RAN, BAL-pk for BAL, ...) : it can be interesting to store the result of matching (as percent for example)

a french address belongs to a territory (area defined by french postal repository as combinaison of optional old municipality, zipcode and municipality) and is maked up :

• a street (mandatory)
• a housenumber (optional), which can have an extension (a letter A-Z, or BIS, TER, QUATER, ...)
• a complement (optional)

hope this model will be compliant for all countries :blush:

[christopheprudent]

works about are available into wiki: https://github.com/christopheprudent/pow/wiki/DB-model

[christopheprudent]

association of address to one or more other referential (entity: address_cross_reference) can be useful to store results of miscellaneous matches, like:

• IRIS
• cadastral register (parcel)
• what3words (https://what3words.com), but 1..n
• ...