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 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.

Answers


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.


Need Your Help

.NET or COM HID iCLASS Smart Card Reader

c# .net smartcard winscard iso-15693

I have coding I almost always use with my Omnikey RFID CardMan 5321 smart cards. Problem is we received new cards today which are marked "HID iCLASS GL" which do not appear to be working well with...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.