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