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.
The problem that now arises is that there is an expected correlation between the "languages" and "translations" tables - there should be a row in languages for each column of translations. AFAIK (could well be wrong, I am no expert in db theory) there is no real way to express in the ddl. Not ideal.
An alternative layout would now be to lose the "translations" table, and have two tables in place; one called "base_text" containing the text to be translated, and another called, say, "tx_text" which contains the translations. Each row of "tx_text" references both "base_text" and also "languages".
This looks like a nice layout, as there is an abstract rep of the languages, and we lose the "translations" table which can get very wide. It's nice that the schema doesn't actually change to add a new language.
BUT there are certain invariants that need to be enforced. The main one is this:
There must only be one row in "site_text" for any given language referencing a given row of "base_text".
How can I enforce this? Also have to bear in mind that there COULD easily be two translators working on the same language. It is imperative that they are not able to simultaneously save a translation of the same base text.
I would also like to have a column in "languages", type boolean, called "is_base" - this says what the base language is. Here, only ONE row can have a true value. (Obviously it has default value of false and is not null).
Another invariant now comes in - the language referenced by every row of "site_text" MUST have "is_base" set to FALSE.
If anyone can tell me how best to express to handle this stuff in postgresql, I'd be grateful. Also general comments on whether this is a good schema or not are welcome.
regards, and thanks in advance
Daniel
--
Daniel McBrearty
email : danielmcbrearty at
gmail.comwww.engoi.com : the multi - language vocab trainer
BTW : 0873928131
