Multilingual Data

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
Ree
Forum Regular
Posts: 592
Joined: Fri Jun 10, 2005 1:43 am
Location: LT

Multilingual Data

Post 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?
foobar
Forum Regular
Posts: 613
Joined: Wed Sep 28, 2005 10:08 am

Re: Multilingual Data

Post 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.
Ree
Forum Regular
Posts: 592
Joined: Fri Jun 10, 2005 1:43 am
Location: LT

Post 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Multilingual Data

Post 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.
foobar
Forum Regular
Posts: 613
Joined: Wed Sep 28, 2005 10:08 am

Post 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.
Ree
Forum Regular
Posts: 592
Joined: Fri Jun 10, 2005 1:43 am
Location: LT

Post 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.
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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..
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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?
Post Reply