[thelist] SQL: Need help for query within a store
rudy
r937 at interlog.com
Fri Dec 6 07:35:01 CST 2002
>> if, however, base cost varies by zone, then simply add
>> another field to your item_shipping_zone_assoc relationship table
>
> So do you mean have a 3-way PK on this table?
> item, shipping zone, pricing zone?
no, a second data column
based on your explanation of the countries involved, perhaps it is better to
change your shipping_zone table, rather than implement a zone-country
hierarchy
item (item_id, name, description, cost)
zone (zone_id, name)
item_zone (item_id, zone_id, shippingcost, overrideprice)
since you were dealing with only 3 shipping zones (US, Canada, and
Hawaii/Alaska) and only 2 pricing countries (US vs. Canada), just keep three
entries in the zone table and add an override price, which will be null if
there is no difference from the base cost in the item record
use paul's idea of coalescing the override prices (nice one, paul)
then your existing query needs only a slight modification, eliminating the
extra lookup, and you're good to go
rudy
More information about the thelist
mailing list