I am interested in having some internationalization capability to an
application. i18 will take care of the interface but not the data. To
internationalize the data, I am wanting to put the internationalized
text strings in a multi_language table like this:
CREATE TABLE multi_language (
id SERIAL,
ml_id INTEGER NOT NULL,
language_id INTEGER NOT NULL,
language_text TEXT NOT NULL
);
Other tables would have be joined by ml_id (the multi language id).
For example:
CREATE TABLE example_table (
id SERIAL,
name_ml_id INTEGER NOT NULL,
So in example_table, name_ml_id would join ml_id so you have the same
ml_id in multi_language table for more than one language. So there
would be two records in multi_language for a record in example_table if
you had an english translation and french translation.
I want to add records to multi_language sequentially. So lets say I
add a new example in example_table, I want to see what the last value
that was added to multi_language was so that if would use the next in
the sequence. As you can see by the structure the id field is serial
and does this but I am speaking of the ml_id field specifically. Let's
say I have one example record in example_table, multi_language would
look like this
1, 1, 1, the brown cow # english translation of name - language 1 (en)
2, 1, 2, la vache brun # french translation of name - language 2
(fr)
ml_id for both record is 1.
So when I create a second record example_table, I want to have this:
1, 1, 1, the brown cow # english translation of name (of example
record - language 1 (en)
2, 1, 2, la vache brun # french translation of name (of example
record- language 2 (fr)
3, 2, 1, the blue turkey #english translation of name (second record -
language 1(en)
4, 2, 2, la dandon bleu #french translation of name (second record -
language 2 (fr)
How best to do this? Would I create a separate sequence for
multi_language ml_id and do a select on it to get the next value before
inserting each multi_language record. Should this be done using a
trigger - if so how? Should this be done in my application code and not
sql or would that be dangerous. For example, the multi_language table
will be used a lot. What if a couple of people were creating new
records at the same time. If I were using python and doing this in my
application code, I am wondering if there could be problems. With a
trigger it would be transactional, correct? Can you have a trigger work
from incrementing a sequence instead of updating a table?
I just want to get this right because it will be an important part of
what I am preparing. Sorry for the really long message but I don't know
if it would make any sense if I did not fully explain what i am
wanting to do. I am not french so excuse my sample translations...
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
