CODEWITHSUNDEEP
databasedatabase-designinternationalizationapplication-design
Umesh Awasthi
Umesh Awasthi

Reputation: 23587

Database design problem for multiple language application

We are working on a travelling portal and for a traveling portal its content and details are the heart.we are planning to have it in multiple locale so this means we need to handle the dynamic data for each locale.

currently we have following tables

Destination
Transport
Places of Interests
user comments etc.

each table contains a lot of fields like

Name
ShortDescription
LongDescription

and many other fields which can contains a lot of data and we need to handle data in locale specific way.

i am not sure how best we can design an application to handle this case,one thing came to my mind is like putting extra column for each locale in each table but that means for a new locale things needs to be changed from database to code level and that is not good at all.

Since each table contains a lot of columns which can contain data eligible for internationalization so my question is what might be the best way to handle this case

edit1 After doing some analysis and some goggling one approach that came to my mind is as below..

i am planning to create a translation table for each table like for destination i have the following design

    table languages
    -- uuid (varchar)
    -- language_id(varchar)
    -- name (varchar)  

    table Destination
    --uuid (varchar)
     other fields which are not part of internationalization.

table Destination_translation
-- id (int)
-- destination_id (int)
-- language_id (int)
-- name (text)
-- description(text)

Any valuable suggestion for above mentioned approach are most welcome...

Upvotes: 1

Views: 564

Answers (2)

nfechner
nfechner

Reputation: 17525

There are several options to solve this:

  1. Extend your tables by a locale column and include the locale in every query. You could even modify the index to include the locale column.
  2. Create a separate table for lokalized strings

I would definitely favor option 1 as it makes the tables still look like the real thing and they can still be read by looking at the data directly. It also prevents you from having to do subselects or joins, which helps performance. Option 2's advantage is only the greater ease for the programmer of the administrative interface (if you have one).

Upvotes: 0

squawknull
squawknull

Reputation: 5191

When internationalizing an application previously, we had a table of locale-based values. So, we did something like this:

Create a table called local_strings, made up of a string_code, locale_code, value. This contains translations of various values. So, if you had translated to three languages, there would be three entries with different locale_codes for each string_code..

Then, each of the master tables, in your case destination, transport, etc. has a reference to a string_code in the local_strings table.

Finally, when querying values out of the database, just be sure that you always join in the local_strings table and always use the user's current locale. Alternatively, you could cache these values in memory so that you don't always join with this table. However, the table never gets that big, in my experience, so there's not much overhead to just join it.

Upvotes: 1

Related Questions