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 -~----------~----~----~----~------~----~------~--~---