[thelist] SQL: Need help for query within a store

[Joshua Olson]

----- Original Message -----
From: "rudy" <r937 at interlog.com>
Sent: Friday, December 06, 2002 8:33 AM


> 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

So, if an item has three shipping zones and two pricing zones, will there be
6 records for this item in the item_zone table (one record per combination)?

The other issue is that one of the pricing zones is really a combination of
two shipping zones.  The pricing zone of US covers the two shipping zones of
US and Hawaii/Alaska.

I appreciate the help, guys.  Definitely food for thought.

-joshua