Ken Greeff Ken Greeff - 3 months ago 11
MySQL Question

Database design for items that have restricted shipping regions

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

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?

product_ship_model

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