2

I'm planing to do all towns, cities, districs, regions states and countrys of almost whole world

So a town belong to a city, a city belong to a district, district belong to region and a region belong to a state, a state belong to a country

I think the best way is to do separate tables per each feature(a table for citys, a table for regions...) with foreign keys but the problem is that not all countrys has states, regions and districs, some only has states, some only regions, somo only just cities, so some cities just will belong only to a country, or just only to a region or just only to a region and distric, so there is a lot of combinations

Any advice?

om-nom-nom
  • 62,329
  • 13
  • 183
  • 228
Colas
  • 157
  • 1
  • 2
  • 12
  • The good news is: you could use a recursive structure. The bad news is: mysql does not allow recursive CTE queries. – wildplasser Mar 21 '12 at 10:56

1 Answers1

0

So a town belong to a city, a city belong to a district, district belong to region and a region belong to a state, a state belong to a country

While that might be true in your country, it's certainly not true everywhere. For example, the country of Japan consists of prefectures; each prefecture is divided into cities, towns, and villages.

Your first decision is whether you're going to model countries the way the countries actually work, or whether you're going to force-fit them into your own schema. Relational data modeling will help you with the first one. For the second, consider adapting the information you'll find through Google's Geocoding API.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • "While that *might* be true in your country, ..." : that's what he states in the third paragraph. – wildplasser Mar 21 '12 at 11:26
  • I have omitted that information but the truth is that I will do as google does administrative_area_level_1 - administrative_area_level_2 - administrative_area_level_3 - City - Subcity (town) – Colas Mar 21 '12 at 11:42
  • @wildplasser: He doesn't say that his own country is set up as town:city:district etc, and he doesn't seem to know that some countries have provinces or prefectures instead of states. In fact, some countries consist of countries. The United Kingdom, itself a country, consists of four countries: England, Scotland, Wales, and Northern Ireland. – Mike Sherrill 'Cat Recall' Mar 21 '12 at 11:47
  • I get your point: in his case levels could be omitted, in your case a whole new naming-hierarchy could exist for a certain region. BTW: I have once created and posted a "flexible topology" data model for this kind of thing, but I seem unable to find it. (it did rely on recursion, IIRC) – wildplasser Mar 21 '12 at 11:54
  • @Catcall, but ISO-3166-1 doesn't recognise them as "countries", they're regions... You'll have more problems with the dependent territories, American Samoa, BIOT, Hong Kong etc. – Ben Mar 21 '12 at 11:57
  • @Ben: ISO isn't authoritative when it comes to recognizing what is and what isn't a sovereign state. It's another case of what I called "your first decision": model countries the way the countries actually work, or force-fit them into your own schema. – Mike Sherrill 'Cat Recall' Mar 21 '12 at 12:29
  • @wildplasser do you made that post here on stackoverflow? – Colas Mar 21 '12 at 13:27
  • Yes, about October, maybe November 2011. It was too advanced to be recognised, so it seems ;-[ – wildplasser Mar 21 '12 at 13:29
  • http://stackoverflow.com/questions/7535062/interesting-tree-hierarchical-data-structure-problem There it is! – wildplasser Mar 21 '12 at 13:57
  • wow man, awesome, heavy work there :), many thanks, i will use it, i was thinking on something like that – Colas Mar 21 '12 at 14:20