Incoherence between the data across tables

[dugzino]

Issue

There's a big issue with the data.
The table cities contains city and town data.
The table states contains state and city data.
And the link between them are wrong.

Found issues in Belgium, Albania, & Japan. So, there probably are in other countries.

Examples

Example 1

For instance in Belgium:

• In states you can find "Antwerp". Which is incorrect as "Antwerp" is a city.
• In cities you can find "Antwerp" and linked to it's parent "Flanders". Which is correct.
• In cities you can find "Borgerhout" which is linked to the state (region) "Flanders". Which is incorrect. "Borgerhout" is a town in the city of "Antwerp".

So, the data for Belgium is completely messed up. Pretty much 90% of it and even fixing the bad IDs won't fix the other issue which is lack of tables and fed data.

Example 2

This time in Albania.
We have duplicates in states like "Tirana County" and "Tirana District".
"Tirana" (or "Tirana district", but it's really just "Tirana") is a city in the state of "Tirana County".
Even though we never make use of the state "Tirana District" it's still there.
We then have "Kavajë District" in states when it's actually the city "Kavajë" and should be in cities.
Also "Bashkia Kavajë" is not even a town. It's even lower level. It's a place in a town.

Solution

The solution would be a whole refactor of the tables and data. Which is huge, but necessary.
It should be like:

continents(
  name: string,
);

regions(
  name: string,
  continent_id: Continent,
);

countries(
  name: string,
  region_id: Region,
);

states(
  name: string,
  country_id: Country,
);

cities(
  name: string,
  country_id: Country,
  state_id: State | null,
);

towns(
  name: string,
  city_id: City,
);

places(
  name: string,
  town_id: Town,
);

Doing this would totally make things a lot more simpler and cleaner to add, edit, and more importantly make use of it.
I wouldn't mind doing a POC, but I got my hands full right now. This should become the next version of this repo.