Page 1 of 1

Multilingual Data

Posted: Mon Dec 05, 2005 4:44 am
by Ree
Which way would you suggest implementing in a multilingual website?
1. Each language gets its own tables: Products_EN (name, price, description), Products_RU (name, price, description), Products_FR (name, price, description)...
2. Tables are the same for each language, but an additional 'language' property is added in each of them: Products (name, price, description, language).
I personally would probably choose the first approach. That way you save a property, and when you need data of a specific language, you search a small specific table instead of a (possibly) huge one. What do you think?

Re: Multilingual Data

Posted: Mon Dec 05, 2005 5:10 am
by foobar
Second option. Saves work, storage space, and is a lot easier to work with. Just have a standardised language field like language = "en-US" or similar. AFAIK, that's how oscommerce does it.

Posted: Mon Dec 05, 2005 5:19 am
by Ree
Saves work, storage space, and is a lot easier to work with.
Saves work - in what way? Storage - probably not important in my case, and probbaly it's minimal, although I'm not sure here.

Re: Multilingual Data

Posted: Mon Dec 05, 2005 5:36 am
by onion2k
foobar wrote:Second option. Saves work, storage space, and is a lot easier to work with. Just have a standardised language field like language = "en-US" or similar. AFAIK, that's how oscommerce does it.
The implication of the original poster's comment about having a table called Products_RU would be that there'll be products with names in a Cyerllic character set. If the tables are all set to en-US the names will get mangled. I would create one table set to UTF8 character set, and convert as necessary. It's a bloody nightmare to debug, but easier than managing lots of tables.

Posted: Mon Dec 05, 2005 5:38 am
by foobar
Ree wrote:
Saves work, storage space, and is a lot easier to work with.
Saves work - in what way?
Having to work with multiple tables for the same stuff is a pain in the a$$. Also, it's not a very clean way of doing things. Imagine you have 50 languages available. That'll get messy quickly and will make for an ugly database. Not very manageable when you need to do maintenance work.

Or let's say you want to add another language. To do that, you'll have to create a separate table which isn't optimal at all.
Ree wrote:Storage - probably not important in my case, and probbaly it's minimal, although I'm not sure here.
Minimal to an extent. At first it will be, but as the tables grow, your db size will increase quite a bit. But nothing really dramatic, though.

Posted: Mon Dec 05, 2005 5:58 am
by Ree
50 languages is pretty unrealistic, but indeed if the site is supposed to grow and different kinds of content is going to be added, the amount of tables can get pretty high. Also adding a new language might be a bit of a problem as well. You guys have a point ;)
Now the only thing that could cause a problem is MySQL 4.0 which is still popular. And it doesn't support UTF-8. But so far I have never had any problems with a host (mine or my clients') not willing to upgrade.

Posted: Mon Dec 05, 2005 8:03 am
by AGISB
I personally add a table row for each language e.g.

Code: Select all

description_us
description_de
.
.
I created a script that goes thru the database and adds a new table row to all necessary tables when a new language is added. The srcipt also reads the other languages displays the content and offers to add the new translation to each item.

Given that I planned it that way so it is not much work and it might not be an option to use in an existing db.

Posted: Mon Dec 05, 2005 9:17 am
by timvw
I prefer generic.properties, generic_en.properties, generic_fr.properties files.. I choose files because it allows us to send them to a translator..

Posted: Mon Dec 05, 2005 9:45 am
by josh
timvw wrote:I prefer generic.properties, generic_en.properties, generic_fr.properties files.. I choose files because it allows us to send them to a translator..

export an XML file for them? I'm going to be doing this pretty soon, anyone have any experience and can advise which is FASTER? Not more convenient?