Storing multi-language geodata in MySQL

Question

My application needs to use geodata for displaying location names. I'm very familiar with large-scale complex geodata generally (e.g. Geonames.org) but not so much with the possible MySQL implementation.

I have a custom dataset of four layers, including lat/lon data for each: - Continents (approx 10) - Countries (approx 200) - Regions/States (approx 100) - Cities (approx 10K)

In relationship to all other tables, I'm properly referencing to four normalized tables of location names, allowing me to expand these separately from the rest of the data.

So far so good... in English!

However, I wish to add other languages to my application which means that some location names will also need translations (e.g. London > Londres > Londre etc). It won't be OTT, perhaps 6 languages and no more. UTF-8 will be needed.

I'll be using Symfony framework's cultures for handling interface translations, but I'm not sure how I should deal with location names, as they don't really belong in massive XML files. The solution I have in mind so far is to add a column to each of the location tables for "language" to allow the system to recognise what language the location name is in.

If anyone has experience of a cleaner solution or any good pointers, I would be grateful. Thanks.

EDIT: After further digging, found a symfony-assisted solution to this. In case someone finds this question, here's the reference: http://www.symfony-project.org/book/1_0/13-I18n-and-L10n

Answer 1

I'm not familiar with what Symfony has to offer in specific functions in that department. But for a framework-independent approach, how about having one database column in the locality table holding the default name for quick lookup - depending on your preference, the english name of the locality (Kopenhagen) or the local name (København), and a 1:n translation table for the rest, linked to each locality:

locality ID | language (ISO 639-1) | UTF-8 translation

12345       | fin                  | Kööpenhamina
12345       | fra                  | Copenhague 
12345       | eng                  | Kopenhagen
12345       | dan                  | København

?

That would leave your options open to add unlimited languages, and may be easier to maintain than having to add a column to each table whenever a new language comes up.

But of course, the multi-column approach is way easier to query programmatically, and no table relations are needed - if the number of languages is extremely probable to remain limited, I would probably tend towards that out of sheer laziness. :)