Multilingual Content on a WebSite

Question

I'm writing a survey website where users are asked multiple choice questions.

Designing the database schema is pretty trivial in the case of a single language, but as soon as there can be multiple, the schema becomes fuzzier.

My gut is telling me that I'd have the following tables (with all the nasty bits removed):

questions (id)

choices(id, questionid)

questiontranslations (questionid, languagecode, content)

choicetranslations(choiceid, languagecode, content)

answers(userid, questionid, choiceid)

Now, I know this would work, but I can't help but think that the # of tables is ballooning unnecessarily.

Does anyone have suggestions on different ways of doing this, or ways of improving the approach?

Note: The # of languages is fixed at launch, but may grow over time.

Answer 1

The schema looks fine. You could also use a unique key for each question and choice and then just have a table with key, value, locale (language - country identifier). This would reduce your translation tables by one. It would also make a generic translation system such that the schema for the questions is separated from the schema for translations.

We employ a similar schema for a general translation service for our applications which has an additional table for monitoring translation misses such that if the service cannot provide a translation in the locale requested the key and locale are logged.