Last few weeks I've been searching te web for information on how to design a database for the use in a multilingual website.
The database will be used to present webpages in English, German, French, Spanish for starters. Other languages will be added in the future. The web user must be able to switch languages at any time.
I can't find any information on this subject. I'm I looking in the wrong places?
I've quite some experience designing and programming Coldfusion + and PHP+MySQL internet sites and intranet sites, so I'm not looking for 'newbie' information. I'm looking for best practices, good books or whatever help is available on the subject.
Yes, I already found these projects, but they are focussing (like many other projects) on the translation of a word in one language into another language, using some sort of common index for all languages.
For my project I'm interested in presenting English text on the English part of the website, Spanish text on the Spanish part and so on. So my texts are stored in the database (the translations are done already) and additions/modifications for each text will be made by different editors using a content managment system for the database.
Maybe I'm trying to make it more complicated than it is and just make a similar set of tables for each language the website uses?
Here is what I have done to incorporate multi languages on the sites I work on. I create separate language files for each language and then include the language file according to which language the user chooses. In the files I just have one language array.
I have never made multiple-language sites, nor do I know how your site is structured, but if I kept content like articles in the DB I'd do something like this:
CREATE TABLE language(
language_id CHAR( 2 ) NOT NULL,
language_name VARCHAR( 100 ) NOT NULL,
PRIMARY KEY( language_id )
-- content would be language independant stuff if you had it
-- this table is optional... was trying to think that something like
-- the 'head' of the table would have a lot of the same information (hidden HTML)
-- and maybe only the page title would change based on language
CREATE TABLE content_header(
content_name VARCHAR( 100 ) NOT NULL,
PRIMARY KEY( content_name )
CREATE TABLE content(
content_name VARCHAR( 100 ) NOT NULL REFERENCES content_header( content_name ),
content TEXT NOT NULL,
language_id CHAR( 2 ) NOT NULL REFERENCES language( language_id )
Then when you want the article you just do:
WHERE content_name = 'intro_paragraph'
AND language_id 'EN'
Good approach! I guess since roel originally requested a database solution your answer is more befitting. I have read many of your posts and have always liked what you have had to say. On that note, let me ask you:
1. For static content on the site would it really be quicker to implement a db solution? For example, on www.fanhome.com in the navigation bar on the left the links forums, chat, mailbox, fanshop, affiliates, about us, and contact us would never really change so why would one want the overhead of retrieving info from the db.
2. In addition, would putting all your content in the db be more work than neccessary. Creating forms to input the data and such seems like a lot of work.
Well, we've found that keeping it in the DB is adventagious because it allows us to add web servers without worrying about figuring out how to get the HTML to the boxes (make a change in one location and all servers get the new data) and allows us to segregate the HTML and code. Since our pages already require DB use for pretty much everything there is no extra network overhead or anything for opening a new socket. I'm not convinced, and our tests don't show a noticeable change, in overhead of requesting some HTML from the database. If we had to do it all over I'd probably continue to use the database since I don't have to worry about FTP'ing templates to multiple boxes, etc. Plus I have database reporting and auditing tools in which to monitor who does what to the HTML for a little bit more security (perhaps).
I think it probably shows a return in the long run due to the initial overhead of developing forms and such (which actually doesn't take long at all).
Meanwhile, I had to go on and I took about the same approach as MattR.
For the tables which hold content in different languages, I finally made the primary key on two columns: an _ID column (INT) and a Language_ID column (TINYINT). It's very easy now to retreive the same 'content row' in a different language, as they have the same _ID, but different Language_ID's.
Although I won't expect users from the website will switch languages all the time, it's merely intended for the content management system for this site.