Database design for items that have restricted shipping regions

Question

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 a) Country b) State c) Region

I am thinking to go with a structure like this:

product_shipping_restrictions - 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'

and

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!

Answer 1

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?

Answer 2

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.

Answer 3

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)
;