Обсуждение: Advice on structure /sequence / trigger
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...
David Pratt <fairwinds@eastlink.ca> writes: > 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... FWIW I started with a design much like this. I threw it out when I started having to actually use it and found it just entirely unworkable. It was bad enough that every single query had to do a subquery for every single text field but the first time I actually had to *load* the data I realized just how much work every single insert, delete, and update was going to be... I ended up storing every text field as a text[] and assigning each language an index into the array. This only works because in my application everything will have precisely the same (small) set of languages available. If you have a large variety of languages and each string will be available in a varying subset then this model might not work as well. It did require a bit of extra work handling arrays since my language driver doesn't do handle them directly. I can't make a principled argument for this being the "right" model but it's working well in practice for me and I can't see the fully normalized model ever working out well. One thing that worries me is that neither the array feature set nor the i18n feature set is stable yet and future changes might break my code. But I think it'll be workable. -- greg
Hi Greg. Sorry for getting back to you so late on this. I think your idea on the design is spot on since it will give me referential integrity with my other and the multi-language will just be a simple two field table with id and multi-dimensional array of language codes to string. Super idea! Even if the array gets larger it is not a big issue since postgres can easily handle it. Regards, David On Friday, June 17, 2005, at 12:15 AM, Greg Stark wrote: > > David Pratt <fairwinds@eastlink.ca> writes: > >> 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... > > FWIW I started with a design much like this. I threw it out when I > started > having to actually use it and found it just entirely unworkable. It > was bad > enough that every single query had to do a subquery for every single > text > field but the first time I actually had to *load* the data I realized > just how > much work every single insert, delete, and update was going to be... > > I ended up storing every text field as a text[] and assigning each > language an > index into the array. This only works because in my application > everything > will have precisely the same (small) set of languages available. If > you have a > large variety of languages and each string will be available in a > varying > subset then this model might not work as well. It did require a bit of > extra > work handling arrays since my language driver doesn't do handle them > directly. > > I can't make a principled argument for this being the "right" model > but it's > working well in practice for me and I can't see the fully normalized > model > ever working out well. One thing that worries me is that neither the > array > feature set nor the i18n feature set is stable yet and future changes > might > break my code. But I think it'll be workable. > > -- > greg >
David Pratt <fairwinds@eastlink.ca> writes: > Hi Greg. Sorry for getting back to you so late on this. I think your idea on > the design is spot on since it will give me referential integrity with my other > and the multi-language will just be a simple two field table with id and > multi-dimensional array of language codes to string. Super idea! Even if the > array gets larger it is not a big issue since postgres can easily handle it. I don't think that's my idea at all. Basically my model does not normalize the localized strings at all. It stores them in place in an array using fixed indexes for each language. It doesn't have a separate multi-language table or id numbers for translated strings. -- greg