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!
Add a comment
|
2 Answers
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
3 Comments
yoda
translation files, altough I'm not sure it would be good to use in cases like products, might be an interesting approach to try :)
Problematic
With caching, the performance hit can be minimized, and be more easily configurable than a database.
Chris Baker
How could this be possibly "more easily configurable than a database"? This is a database. Using files is just another form of storage for a database of information.
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.
5 Comments
Chris Baker
What do you mean? This is exactly how I "handle different database language contents" - one item with several potential languages. Unless I am grossly misunderstanding your question, in which case I would ask that you clarify your wording. The concept is called "internationalization" or "localization"... pretty common fare.
yoda
I asked about the db scheme. Your answer seems to suggest that I have an entry per procuct, wich will become heavy for the system with time.
Chris Baker
Internationalized systems are inherently heavier than non-international systems. You will, or rather ought to, have a database table that will have multiple entries per product. There's just no getting around it. Putting it in files is, aside from being messy and needlessly involving yet another data access method, the exact same thing as adding rows in a database table. There is no performance loss here because a) you'll presumably establish indexes on the database tables and b) are filtering your results with conditionals to only deal with one row of data.
Chris Baker
Indeed, my answer and the one that was added a few minutes after mine are the same exact solution - you have a list of products and a list of localized product names. The only real difference is that I would never suggest files, and my solution uses a fallback in the absence of a localized item. At any rate, do what you will, hope you got the answer you needed one way or another :)
yoda
I ended up not doing thig my way : a pivot table to intersect languages and content.