Hi I have a number of tables. I am interested in having some
internationalization capability so that the application and it's data
can be in more than one language. i18 will take care of the interface
but not the data. To internationalize the date, 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)
Ok now the questions. 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 would 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. Oh, and I am not french so excuse my sample
translations...
Regards,
David
