Re: Database design problem: multilingual strings - Mailing list pgsql-general

From Russ Brown
Subject Re: Database design problem: multilingual strings
Date
Msg-id oprq98wmb76sifx0@relay.plus.net
Whole thread Raw
In response to Database design problem: multilingual strings  (Antonios Christofides <A.Christofides@itia.ntua.gr>)
List pgsql-general
You might want to try abstracting the languages further, and have a central
store of strings for everything.

E.g. something like:

languages
id   handle   ------------
1    en
2    fr
3    es

strings
id   handle
-------------
1    FORK
2    SPOON
3    LARGE_ESSAY

string_translations
id    string_id     language_id     translation
--------------------------------------------------
1     1             1               Fork
2     1             2               Forchette
3     2             1               Spoon
4     2             2               Cuilliere
5     3             1               This is a large essay in Engligh. Same
would apply in any other language.

cuttlery_types
id    description_string_id
-----------------------------
1     1
2     2


Then you can use a query like this to get an array of cuttlery types in any
language:

SELECT cuttlery_types.id FROM cuttlery_types,
       string_translations,
       languages
 WHERE cuttlery_types.description_string_id=string_translations.string_id
   AND string_translations.language_id=languages.language_id
   AND languages.handle='fr';

Just swap the 'fr' for 'es' or whatever for a different language. Use the
same central store for all other text fields in the database that needs to
be language-independant. If you want to allow for missing values just use a
LEFT JOIN or similar. You could also write a fairly simple query to give
you a list of missing translations, which could be handy.

HTH,

Russ.



pgsql-general by date:

Previous
From: Karsten Hilbert
Date:
Subject: Re: postgres 7.3.3 problem - not talking across port
Next
From: "Carlos Oliva"
Date:
Subject: Re: Eliminating start error message: "unary operator