CMS for site in several language versions - best approach
Posted 03 March 2006 - 04:47 PM
I have a tricky problem - at least for me.
I'm building a cms for a site in several languages, english, french, etc - the same content is delivered in all languages. In the site that's open to the public (not the admin one) each page will have flags for that same content, eg. an event, in another language. So if i'm seing event number 112 in english, and i click the portuguese flag i should see the same event (112) in portuguese.
The problem is how to best set up the mysql db.
The solution i have now is for each table i have several fields fo each language, eg. events table, i have the eventid, eventimg, en_eventtitle, en_eventtext, fr_eventtitle, fr_eventtext, etc, etc. This makes sure that the same event has the same eventid in all languages, but the table gets huge (5 languages in total, makes 12 fields more or less in each table). Each text (be it en_text, fr_text can have about 1000 characters). To go around this problem i do a different select query depending on the language, selecting only the fields i need (eg. in the english version of site, i select only eventid, eventimg, en_eventtitle and en_eventtext). When i do an insert i insert fields in all languages even if only one has been filled out (too complex to vary the select statement based on the various possibilities).
I have about 10 sections: events, banners, galleries, discounts, press, client's opinions etc.
Another approach would be to split each table/section in two. Taking the same table events as an example, i could have a 'main' events table with 2 fields: eventid, eventimg (the picture). Then i could have a 'child' table, events_languages with the fields: id, eventid (to relate to the main events table), title, text, language (eg. 1 for english, 2 for french, etc). The tables wouldn't be so huge but this would lead to 2 tables per section, considering i have 10 sections, 20 tables. The drawback here is that the administration of the site would be more complex (select fields from two tables to show all events, for example), and the number of tables in total would be big. The advantage is that adding another language would be easier and dynamic, and each table would 'weigh' less making the select statements possibly lighter.
If i wasn't clear please say so. English isn't my mother language I'm portuguese.
Thank you for any, really any, help
Posted 03 March 2006 - 07:28 PM
Posted 03 March 2006 - 08:01 PM
Was a bit stuck.
Really appreciate it.
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users