I cannot cunningly (flexibly, universally, reliably, without "your ****, you have to break everything to correct it for Zimbabwe") construct a database for storing settlements of any country in the world. The standard structure consisting of tables: "country", "region", "city", and the corresponding fields that link the parent to the children does not roll here.
The problem is that each country in the world has its own division into administrative units, for one country it is: region, district, city | village | settlement; and in the other: state, city. Wikipedia, as it were, shows that all countries have 4 levels of nesting in the admin hierarchy. units, that is, the first thing that comes to mind is not to be tied to the area | state, but simply to the nesting level (admin unit of the 1st level, 2nd, 3rd, 4th), but in the table on Wikipedia next to the 4th level column it says "The fourth level and less" – it means that this is no longer a fixed hierarchy of 4 levels, but there can still be many levels of nesting, respectively, the greater the depth, the more problems when obtaining data, since you will have to climb back and forth along this tree.
I would really appreciate your help! Maybe someone has already solved this problem by stuffing bumps and can advise something sensible so that I do not step on the same rake.
A database with data from "all countries" like geonames.org does not need to be advised, since it is full of rubbish and unnecessary | missing data, at this stage I need the structure of the tables itself to draw it on a piece of paper, and already have a good idea of what problems there may be when using it, and how to sharpen it for your own needs.
In fact, everything degenerates into simple storage of the graph, because this is what it is:
- feature table: id, type, center, polygon, metadata
- link table: parent id, child id
- (optional) name table: object identifier, locale, name
In general, you don't need anything else. The format of the administrative division can be taken straight from Google (they just have an administrative_area with five division levels, which are needed not to say that often), you will never be able to validate the structure of objects (because it likes to disobey formal rules), therefore, it remains only to use the general solution and rule by hand. For cases like the coincidence of the name of the region and the capital city of the region, you can put the
implicit parameter in the metadata, by which the region will simply not be displayed, for cases of constant updating of the region from some API, you can add the
manualUpdate parameter to the metadata. Honestly, I don't even know what to advise next. It is possible that a graph database will perform better, although normal databases are likely to be fine.