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 sqlalchemy@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
