I am designing the database for a shopping cart and I am having trouble deciding which way to approach this problem.
There are three tiers that can items can be restricted to:
1) Local Delivery Only
2) Shipping Available
I am thinking to go with a structure like this:
key(int), productId(int), local_only(enum('y', 'n'), countries(enum('y', 'n'), states(enum('y', 'n'), regions(enum('y', 'n')
Then if there is a flag for any of them check the corresponding table e.g.
product_shipto_states - key(int), productId(int), stateId(int)
So for example if product 10 is restricted to only ship to Australia and the states NSW and QLD we would have:
product_shipping_restrictions - NULL, 10, 'n', 'y', 'y', 'n'
product_shipto_countries- NULL, 10, AU
product_shipto_states- NULL, 10, 1 & NULL, 10, 2
Can you guys think of a better way to achieve this result?
P.s. Sorry for the formatting!
It may help to use table of allowed destinations, instead of focusing on restrictions. The geography table lists all possible destinations that one may ship to. The allow_shipping table defines allowed destinations for each product. Note that term "local only" is a bit ambiguous -- local to whom?
create table product ( ProductId integer not null , ProductName varchar(128) ); alter table product add CONSTRAINT pk_product PRIMARY KEY (ProductId); create table geography ( GeographyId integer not null , Country varchar(50) , State varchar(50) , Region varchar(50) ); alter table geography add CONSTRAINT pk_geography PRIMARY KEY (GeographyId); create table allow_shipping ( ProductId integer not null , GeographyId integer not null , Allowed enum('y','n') ); alter table allow_shipping add CONSTRAINT pk_allowshipping PRIMARY KEY (ProductId, GeographyId) , add CONSTRAINT fk1_allowshipping FOREIGN KEY (ProductId) REFERENCES product (ProductId) , add CONSTRAINT fk2_allowshipping FOREIGN KEY (GeographyId) REFERENCES geography (GeographyId) ;
You could have special rows in the products_shipto_countries and product_shipto_states tables to show 'any' or 'all' or 'none', and then you wouldn't have to worry about whether or not to check those tables; you'd do so all the time. That would leave fewer paths through the code, at the expense of perhaps unnecessary reads.
An additional question would be how to decide you can ship a product to anywhere except certain countries, states, etc. Would you want to list all the possibilities or have 'not' rows in your tables?
Perhaps this is one of those cases where an EAV table could be useful.
Enitity, Attribute, Value where Attribute is the Code of the country
product, country, state
10, AU, NSW 10, AU, QLD 10, US, ALL
Not exactly sure how it could handle regions unless States could be made unique in some fashion.