Design Database Architecture: DB for use with a couple different languages

Question

I have a web site that should have translation of the same content on different languages. So it should have the databases entries with different translations of same strings. It is not just a small website, so there is a lot of different complicated data structures.

I have one idea how to realize this. But I don't really like it.

I think to introduce an additional Translation table, where plan to store string fields on different languages.

For example for table Project, that contain three string fields(name, shortDescr, fullDescr) I will use Translation table in the next way:

I will change name, shortDescr, fullDescr fields from string to integer(that contain link(ID) to translationTxtID). Different translationTxtID and lang fields will define the unique string for each string token and language. So this solution will work, but I am looking for more elegant solution. Can you suggest me a solution of this problem.

Answer 1

Why not have multiple entries in the tables, one for each lang? The PK can be a combo of ID and LangID. For your example above:

Project

ID, int
LangId, int
Name, varchar
shortDesc, varchar
Fulldesc, varchar
date, date

Then all you need to do in your code is set a language variable, and in your queries feed it as one option in the query (I'm using SQL as a reference here):

SELECT (columns)
FROM Project
WHERE ID = @id
and LangId = @langid

You keep langid through all the queries for that particular session.

Answer 2

Can you suggest me a solution of this problem?

Yes.

Don't use integer keys. Indeed, don't invent this yourself. Just use gettext. You already have it available on just about every OS there is. It's fast, proven software you don't need to write.

Do what the standard gettext module does for i18n. (see http://en.wikipedia.org/wiki/GNU_gettext)

1. Use Text Keys.

2. Pick a Locale (i.e, the "C" locale, or the locale in which you wrote the software).

3. Put all the messages into your "Project" table as strings in the default locale.

4. Put all the translations into the "Translation" table with the original string and the I18N locale to translate it to. Yes the Translation table is keyed by a big, long string. This works great in practice because (1) you don't have that many strings, (2) you don't look them up all that often, and (3) you should be using gettext, not rolling your own.

5. When you present any data to a user, you attempt a SELECT to get the translation. If you find the translation, that's good.

   If you don't find the translation, then they key you're using is the default string, which is better than nothing. Log the exception somewhere, and present the original string.

Answer 3

I use a localization table for each parent table that contains strings. So if you have a table "Project", you would also have a table "Project_Locale". Project_Locale has the same PK as Project + the addition of a "Culture" field. All the localizable string fields go in Project_Locale and everything else goes in Project.

Answer 4

My choice is

Project
-------
ProjectID (PK)
Date

ProjectLoc
----------
ProjectID (FK)
Lang
Name
ShortDesc
FullDesc

Then you can run a simple query like

SELECT Project.ProjectID, Date, Name, ShortDesc, FullDesc
FROM Project
LEFT JOIN ProjectLoc ON Project.ProjectID = ProjectLoc.ProjectID
WHERE ProjectLoc.Lang = %CurrentLang%

Pros: Elegant and simple
Cons: Large number of tables