Im interested in these ideas, would you be willing to say a little more? Are you talking about allowing the product_variations to be either global or unique depending on a flag set?
Regards Peter On Thu, Jul 16, 2009 at 9:50 AM, Neven MacEwan <[email protected]> wrote: > > Peter > > I think you have to get away from the "Global v Unique" mindset, For > example in the situations you gave you could alway insert amother table > "classof_product_variations", ie any number of levels of groupings of > product_variations, how you structure this depends on where you wish the > update logic to apply > > If you want to do it in the app, just have a 'fine grained' database > If you want to reflect it in the db structure, group by classes > > HTH > > Neven > > > Ive been hit with a quandary twice this week and wondered if theres a > > creative compromise im missing here. > > > > Say i have a mysql data structure like this: > > > > products > > <---product_variations > > <--product_variation_options > > > > product_variations has products as a required parent > > product_variation_options has product_variations as a required parent > > > > Now this is an example of what im calling a unique relationship. The > > options > > are unique to the variation which is unique to the product. Different > > products can not have the same variation. This allows each variation set > > to > > be completely independant giving complete flexibility, but requires each > > one > > to be defined and changed seperately, in some situations a tedious > > process. > > The problem sets in when i want to change one product_variation_option in > > all product_variations that use it. A function to search for and update > > these instances is often the result. > > > > OTOH if i have a structure like this: > > > > products <---> product_variations > > <--product_variation_options > > > > and use a linking table: > > > > product_variation_links > > ---------------------------------- > > product_id > > product_variation_id > > > > so that a product can have any variation, and any variation can have any > > product, then this is what im calling an example of a global > relationship. > > If i change the variation, all its parents are effected at once, easy to > > update in cases where the variation is say a standard colour pallete, or > > if > > cross product changes are frequent, and required to be easy. > > > > Until now ive felt that ive been forced too choose one or other of these > > models, and using as a deciding factor, how often the data is added cf > > changed. > > > > Another example: > > > > treatments > > <--> formulas > > <-- formula_components > > > > A global relationship that means that any treatment can have any formula. > > That allows me to change the formula once and all treatments that use it > > are > > changed. But what if i need exceptions? So far, what i do is fork the > > formula, to create a new formula as a copy of a "base formula". That way > > changes to the "base formula" no longer efffect the new forked version, > so > > now it has the flexibility to be different but that difference means it > is > > no longer related, nor can be maintained as a group. > > > > Now lately two different projects have pushed this model to its limit, > > that > > seem to call for some better hybrid of these two models. > > > > The flexibility to customise unique formulas, and yet maintain the > > formula_components extensively and uniformly. In this case the tension is > > between allowing each treatment to have a sufficiently customised > formula, > > and yet be able to maintain large groups of treatment formulas over time > > as > > the components or formulas change. Im not sure if this is terribly > > coherrent, but i guess if youve ever been faced by this quandary it will > > make sense. > > > > Failing ideas, is there any recomended reading on database table > > structuring. > > > > Peter > > > > > > > > > > > > > --~--~---------~--~----~------------~-------~--~----~ NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected] -~----------~----~----~----~------~----~------~--~---
