+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: help in multi language website (/showthread.php?tid=32013)
help in multi language website - El Forum - 07-10-2010
[eluser]no one[/eluser]
what is the best way in designing database for a website supporting only 2 language
i have two options and need which one is the best
for example (news table)
1- create only one table containg all news in both language and make new column to distiguish betwwen which one will be in the english site and which will be in the french
ie: id,title,Publishing_date,language
where language column define the language of this record
2- create two table for each language ie: i will have two table "news_en" for news which will appeare in english site and onther table "news_fr" containg news which will appeare in enlish site
help in multi language website - El Forum - 07-10-2010
[eluser]iancant[/eluser]
In a site that I have used, with multiple languages I have opted for your 2nd approach. I use the same application to deliver all the languages, but implement a hook pre-system which identifies the language the visitor is using.
I then save this as a constant which my application appends on to all database tables to get the correct language version and updates the code igniter language variable for lang files.
I then have a controller which allows the user tgo switch between languages.
I find this works perfectly on DataMapper DMZ where I just sufix the table name on to the end of the table in my models.
help in multi language website - El Forum - 07-10-2010
[eluser]no one[/eluser]
but in the 2nd approach if you want to add new language to your website you must create new tables for this language
help in multi language website - El Forum - 07-10-2010
[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.