Tips for Database Design of Website with international Users

Question

Hello Friends,

i am developing a dating website, in which users can register from all over
the World. The Dating section is divided into cities from again, all over
the World.
The Problem i see here is, i do not have a database that covers all cities
around the Globe.
I am confirming every user by hand so this gives me the opportunity to review
the users city and country.
Main main approach is, giving each city a "city_id" in the Database.

It's like if a person joins from New York, i give it the id US_NEWYORK
If the person joins from Frankfurt they get the id DE_FRANKFURT

Both of those users still have their own City name in the Database and it's
written down the same as they wrote it. So for example,

"Frankfurt" instead of "Frankfurt am Main" for the right name or,
"NYC" instead of "New York" for the right name.

This way i can keep every users writing and can give the cities a special id
and run all queries through that ID.

This is my very own solution and i think it's not so bad. Do you might have
an Idea what i could do better?

I would like to kindly remember, that i would need every city from every country
i'm running this service, if you would like to recommend i would get a database
from somewhere :)

Thanks for reading.

Answer 1

Why don't you use a geocoding servers like geonames.org or Google's geocoding API to find a canonical name for locations, and also a latitude/longitude. I assume you need the locations in some sort of canonical form so you can find closest matches, right? Well, latitude/longitude is the way to go there.

For instance, if you ask Google about "Frankfurt am Main", like so:

curl 'http://maps.googleapis.com/maps/api/geocode/xml?address=Frankfurt%20am%20Main&sensor=false'

You get back all this:

geocode/xml?address=Frankfurt%20am%20Main&sensor=false'
<?xml version="1.0" encoding="UTF-8"?>
<GeocodeResponse>
 <status>OK</status>
 <result>
  <type>locality</type>
  <type>political</type>
  <formatted_address>Frankfurt, Germany</formatted_address>
  <address_component>
   <long_name>Frankfurt</long_name>
   <short_name>Frankfurt</short_name>
   <type>locality</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Frankfurt am Main</long_name>
   <short_name>Frankfurt am Main</short_name>
   <type>administrative_area_level_2</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Hesse</long_name>
   <short_name>HE</short_name>
   <type>administrative_area_level_1</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Germany</long_name>
   <short_name>DE</short_name>
   <type>country</type>
   <type>political</type>
  </address_component>
  <geometry>
   <location>
    <lat>50.1115118</lat>
    <lng>8.6805059</lng>
   </location>
   <location_type>APPROXIMATE</location_type>
   <viewport>
    <southwest>
     <lat>49.9968858</lat>
     <lng>8.4243871</lng>
    </southwest>
    <northeast>
     <lat>50.2258641</lat>
     <lng>8.9366247</lng>
    </northeast>
   </viewport>
   <bounds>
    <southwest>
     <lat>50.0155200</lat>
     <lng>8.4727150</lng>
    </southwest>
    <northeast>
     <lat>50.2269512</lat>
     <lng>8.8004960</lng>
    </northeast>
   </bounds>
  </geometry>
 </result>
</GeocodeResponse>

Answer 2

http://www.geonames.org/

looks quite comprehensive.