Re: advice on schema for multilingual text

Daniel McBrearty wrote:
> Hi
>
> I have a website that has multilingual text stored in the database.
> Currently I just have a flat table (lets called it "translations"), one
> row per text item, one column per language. This works OK, for now, but
> I am looking at a redesign. Mostly I want to keep information about the
> languages in teh db as well, so that look like an extra table, one row
> per lang.

Not sure about your base and site text ;) But I can tell you how I
solved a similar problem:

I used tables:

languages (language_id serial, description_id int4) references
descriptions (description_id)

descriptions (description_id serial,acronym text)

descriptiontexts (description_id int4, language_id int4, description
text) references descriptions (description_id), languages (language_id)

(pseudocode)

so the languages table keeps all the languages I use in the database,
(as ids) for example:

languages
language_id | description_id
1 1
2 2
3 3

descriptions
description_id | acronym
1 lang_en
2 lang_de
3 lang_ru

discriptiontexts
description_id | language_id | description
1 1 english
1 2 Englisch
2 1 german
2 2 Deutsch

thats for the self documentation - now you can
translate sentences (give them an acronym for
use, like 'customer_basket_close' and let your
translaters translate them with respective language_id
into descriptiontexts table. This way for a given
expression (description_id) there can only be one
entry per language.

HTH
Tino Wildenhain