CMS for site in several language versions - best approach

[yonta]

Hi

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.

Any hints/suggestions?

Thank you for any, really any, help :)

Sofia

[fenway]

Very well explained. IMHO, go with the second option. Like you said, it makes adding languages very easy, and having a single UID for each "object" regardless of language is also useful -- otherwise it would be very hard to, say, show all languages instances for a particular object. Twenty tables is nothing for a DB, and it's easier to simply say "SELECT *" instead of having to request the subset of language fields. It's not a big deal to have JOINS to get all of this info. Just store anything non-language related in the parent table. Good luck.

[yonta]

Thanks :)

Was a bit stuck.

Really appreciate it.

Sofia