Tips for Database Design of Website with international Users

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 ve开发者_开发百科ry 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.

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>

http://www.geonames.org/

looks quite comprehensive.