Thread: Design question, Does postgres provide a simpler solution than the obvious?

I have a design issue and I know how I would normally solve it, but it looks
to be a pain in the long term. I'm used to Oracle where the right way is
usually the painful way, but being new to Postgres I'm wondering if there are
any special features (perhaps arrays? or rules?) that make this issue easier.

Basically the issue is that the application needs to work in multiple
languages, so every single text field needs to have N different alternate
values. Usually two languages will be inserted and updated together. If the
desired language isn't present I need to default to another language.

So the obvious fully normalized approach is to have a string table and every
simple single query insert and update becomes a more complex transaction that
inserts or updates multiple records in the string table and stores the key for
those strings in the original table.

I guess my ideal solution would allow me to issue an insert or update on the
original table with some sort of associative array data structure as the value
for the string field. The appropriate work would all get done automatically.
Then selects would either return the associative array or perhaps even
implement the entire logic for looking up the correct string to display.

This is a pretty common problem I wonder if anyone has come up with anything
easier to work with than the standard normalized string table approach.

--
greg

On Monday 07 Oct 2002 11:49 pm, Greg Stark wrote:
> I guess my ideal solution would allow me to issue an insert or update on
> the original table with some sort of associative array data structure as
> the value for the string field. The appropriate work would all get done
> automatically. Then selects would either return the associative array or
> perhaps even implement the entire logic for looking up the correct string
> to display.

Just to clarify, assuming the preferred language is French and the default
English, you want something like:

 id lang phrase
 73   FR bonjour
 73   EN hello
 74   EN goodbye

You then want to be able to do:

SELECT phrase FROM foo WHERE id=73 or 74;
 phrase
=======
bonjour
goodbye

Is this the sort of thing you're after?

--
  Richard Huxton