Re: Database design problem: multilingual strings

At 19:15 24.06.2003, Antonios Christofides said:
--------------------[snip]--------------------
>'description' is no longer enough; it must be possible to add
>translations to _any_ language and to any number of languages.
>I've thought of a number of solutions, but none satisfies me to the
>point that I'd feel ready to die :-) I'd much appreciate
>comments/experience from anyone. I include the solutions I've thought
>of below, but you don't need to read them if you have a good
>pointer in hand.
--------------------[snip]--------------------

Taking off from this table:

table cutlery_types
id description
----------------
1 Spoon
2 Fork
3 Knife
4 Teaspoon

you might use a table set like this:

table lg_dependent
oid_table | column | id_row | language | text
---------------------------------------------------
######### | desc | 1 | en | Spoon
######### | desc | 1 | ger | Löffel
######### | desc | 1 | fr | Cuilliere
######### | desc | 1 | el | Koutali

Use a select statement like this:

select t1.id, t2.text /*, etc */
from cutlery_description t1
join lg_dependent t2 on t2.oid_table = (select oid from pg_class where
relname='cutlery_types') and t2.column='desc' and t2.language='en'

It might be better to _not_ use the table oid to be more portable across
databases, or pg_dumps without OID. You might either use the table name
then, or have another table mapping table names to unique numbers.

--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/