Re: advice on setting up schema sought - Mailing list pgsql-novice

Hi (see below),
> 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, i'm not sure i totally understand your
request.  from what i could make of it, though, would
this workd?

t_text
text_id (pkey)
language_id (fkey)
text

t_language
language_id (pkey)
language

the foreign key of the t_text table would reference
the the pkey (primary key) of the t_language.

every row of multi-lingual text would have an
associated language.

is this a solution that can help you or did i
misunderstand your question?

ps - you lost me at site_text and base_text.

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com