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