PHP & MySQL - Best way to handle different database language contents

Question

The question is as follows : what's the best way to handle different database language contents? Suppose you have a shopping cart whose products may have several languages, what would be the best approach to build the db scheme?

Cheers!

Answer 1

I would store a unique translation key in the table, and then either have another table or perhaps translation files so that you can reference the translation from the key.

table product:

id: 15
name: product.foo_widget
attr: ...
... etc

table translation:

lang: en
key: product.foo_widget
trans: Foo Widget

Answer 2

Have the language of the current user as a property of your user object (or whatever pattern you use for user tracking). When you query the DB for strings, add a conditional to pull the language of the current user if it is available.

In this example, I put the language_id in $_SESSION; I usually would not do user tracking in this fashion but I believe the concept is illustrated. In your database, each product would have multiple entries for each language with 0 being the default of the site (presumably English). The query will grab the language-specific line item or fall back to the site default if there isn't a language-specific item available.

// the id of the item you're after
$item_id = 1;
$sql = 'SELECT id, name FROM product_names WHERE item_id = '.$item_id.' AND (language_id = '.$_SESSION['user']['language'].' OR language_id = 0) ORDER BY language_id DESC LIMIT 1';

I use this same concept for general strings around the site - I have a database table called "content_strings" with the fields id, text, and language_id.