[eluser]iancant[/eluser]
Without a doubt both implementations have their good and bad points. The site I was working on required that the same content be available in different languages. As the site expanded they wanted the ability to add further languages.
In order to comply with this I followed through with approach that stored so-called meta information in 1 table and then a separate table for each language that was available.
Here is an example:
Table: news_meta
Fields: id, creator_id, create_time, last_editor_id, edit_time, default_site_id, default_language
Table: news_english
Fields: id, news_meta_id, headline, article
(both headline and article have MySQL indexes to allow for Full Text Searching)
Please note the above fields are only used to indicate the concept, and not full representation of all the information actually saved to the database.
Here’s a quick rundown of my thought process
* Each language can be stored in a table with corresponding character sets for that language
* If an article is not available in a specific language yet (due to incomplete translations then a default language can be displayed instead)
* All fields where data is stored are able to be searched
* SQL Queries are only conducted in the user’s language instead of a separate field for each language. Imagine hundreds of articles (500 characters say) in 10 languages that’s 5000 characters per article. Say we have 200 articles, that’s 1,000,000 characters which would take longer to search than only 100,000 characters.
To me these performance gains and flexibility outweighed the requirement to add a new table for each language.
