Advice on structure /sequence / trigger

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Advice on structure /sequence / trigger

David Pratt
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 [hidden email] so that your
      message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: Advice on structure /sequence / trigger

Greg Stark-3

David Pratt <[hidden email]> 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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: Advice on structure /sequence / trigger

David Pratt
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 <[hidden email]> 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
>

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: Advice on structure /sequence / trigger

Greg Stark-3
David Pratt <[hidden email]> 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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq