well i've no idea about scalability. it's just general
(semi-theoretical) OO approaches.
your approach with table-per-language seems faster... but how u make a
(switchable) pointer to a table? i'm not fluent in SQL, i dont know
if u can make the Book table point (foreignkey?) to some virtual View
and when changing language switch the View to contain the proper
table for the language - so query-wise it all looks same, replacing
the pointed table with another (but who'll update the foreignkeys
then).
anyway, if u say that 10K objects with one attr per object kills it,
i've no idea about my 100K+ objects with average 5 attrs per
object...
ciao
svil
On Wednesday 12 November 2008 23:43:08 g00fy wrote:
> I already implemented your solution:
>
> Book : table with "general data"
> id, isbn, category_id ....
> BookTranslation: table with "translations"
> id, book_id, title, language
>
> Correct me if this is not what you meant
> ##################################
>
> and this generally works up to 10k objects.
> since I have 10 other multilingual objects connected with Book, it
> was awfully slow to sort and order when the number of books reached
> 1000. This can't be enterprise solution.
>
> Adding language
> ##############
> This can be easily done by just adding another translation with new
> language in it.
> In this case you don't have to do nothing to make it work.
>
>
>
> The best (fastest query) solution is to create columns with
> suffixes like _en _cz _de _pl, create indexes on languages and
> defer other languages columns.
> You can also do a small trick: define property on a class based on
> a function:
>
> def title(self):
> return self.getattr('title_%s' % common.get_language(),
> 'title') [I actually don't know any python so sorry if I am
> mistaken ]
>
> or add column properties while mapping synonym to expected
> language columns to use "Book.title" API:
>
> mapper(Book, book_table, properties={
> 'title': synonym('title_%s' % common.get_language(),
> map_column=True)
> })
>
>
>
> Adding language:
> ###############
> Since this implementation uses columns in one table it is obvious
> that adding new language would require altering the table.
> This might be more complicated but really: how often do you add a
> language?
> Altering table might be done by triggers
> when you add a language to a database, you simply run the altering
> table trigger that creates another set of columns and creates
> indexes on them.
> Of course you would have to copy another set of language columns
> => modify your table definition. But that might not be such a big
> effort. This is only bad when you consider it as a bad programming
> practice ("doubling the code").
>
>
> -------------------------------------------------------------------
>-------------------------------
>
> I am also considering different solution that is a bit similar to
> your proposal.
>
> you store data in multiple tables
>
> Book : table with "general data"
> id, isbn, category_id ....
> BookTranslation_en: table with "translations"
> id, book_id, title
> BookTranslation_de: table with "translations"
> id, book_id, title
>
>
> this will also do better than 2 simple tables since you have less
> join conditions and therefore data to join (search for).
> But this harder to implement in SqlAlchemy. I don't know yet how do
> I tell SqlAlchemy that one model (BookTranslation in this case) can
> have multiple tables, and uses one.
>
> Adding language:
> ###############
> Here we would have to create another table for new language. the
> table definition is all the same only the table name (suffix)
> differs.
>
> -------------------------------------------------------------------
>-------------------------------
>
>
> Still, I am not fully satisfied with this two solutions and I am
> still
> *******************************************************************
>*********** awaiting other proposals.
> *****************************
>
> On 12 Lis, 10:05, [EMAIL PROTECTED] wrote:
> > currency part:
> > i dont know if unified_price has to be in same table or in
> > separate one - or just a calculable property (non-db). maybe best
> > is calculable in separate table, with triggers or (ala
> > SQLAlchemyAggregator) mapperExt's to update it at every change of
> > appropriate rate and/or orig.values.
> >
> > on the multilingual part:
> > beware, this is general approach for very complex system with
> > hundreds of classes/ attributes-to-translate. we've invented 2
> > approaches: * association between the attribute-in-question and
> > the language, carrying the translation, i.e. AssocTable( ref2obj,
> > attr, ref2lang, translation). u'll need a property/descriptor
> > that looks up the attr using current lang (maybe context like).
> > pros / cons:
> > + transparent, i.e. no changes anywhere else, easy to turn
> > on/off; ~ orig.column stays as is, can be used as cache or
> > something - bitemporal aspect has some worries with this, as
> > pointers are inwards to bitemporal obj from the assoc.table - but
> > i guess u're not bitemporal
> > * complex pointer: a separate table is made (attribute,language,
> > translation) - common for all types or one-per-type, and all
> > translatable attributes are pointers/queries to a row in the
> > table using current lang. pros/cons:
> > +~ all can be loaded using simple join given the language
> > - changes the model - extra columns per attribute (or the
> > orig. attr.column becomes a ref)
> > ~ orig.column can be used as cache if pointer is in extra
> > column + bitemporaly this is transparent, as the pointers are
> > outward of the obj, not inwards
> >
> > both have an issue of uniqueness of attr, which can be solved by
> > making it typeid+attrid, except that this wont work for
> > bitemporals. we have done some prototyping on both, but have not
> > reached any state of completeness so far (and it's not in visible
> > must-do horizon).
> >
> > i'll be interested if u find something else, or something that is
> > ready and working.
> >
> > ciao
> > svil
> >
> > On Wednesday 12 November 2008 00:43:00 g00fy wrote:
> > > What is the best way to store content in multiple languages
> > > (translations) ?
> > > The same question about the prices in multiple currencies ?
> > >
> > > The problem:
> > > ############
> > > World Wide Book Shop (collection of books from multiple
> > > countries in multiple currencies and languages)
> > >
> > > Model:
> > > ######
> > > Book
> > >
> > > Question:
> > > #########
> > > Every book has it's own original title and multiple
> > > translations. How do I store in the DB translations?
> > >
> > > Question:
> > > #########
> > > Every book has it's own price in currency of the production
> > > country. How do I store prices with their currencies?
> > >
> > > *Note: I cannot unify prices into one currency, because
> > > currency rate does change and therefore price. I need to store
> > > currency in original form , and then (when searching/filtering,
> > > or daily) unify it to one currency so I can easily order by
> > > price.
> > > Example:
> > > Book1: original price = 10 USD
> > > Book2: original price = 10 EUR
> > > Now order from the cheapest : Book1, Book2,
> > > Now filter where price>=11 USD : Book2
> > >
> > > *Note: the number of currencies and languages is not specified
> > > and is likely to change (the solution should be flexible)
> > >
> > > ---------------------------------------------------------------
> > >---- -------------------------------
> > >My suggested solution about "Prices
> > > and Currencies" is:
> > > to have 3 columns in table:
> > > original_price
> > > original_currency
> > > unified_price
> > >
> > > This way, I would update unified_price daily according to
> > > currency rate and I gain ability to order and filter by price
> > > by bringing search criteria to unified currency.
>
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
