CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Alex
Alex

Reputation: 6645

Storing shipping prices in MySQL table where many columns will be added in future

I'm looking to create a system where users specify shipping prices for their items. The variables I need to cover are the weight band (from in grams to in grams), the price and the countries covered. From this data I can calculate shipping cost by referencing the customers country and the total weight of the basket.

My first thought is something like this:

id      from_weight to_weight   price   us  ca  gb  fr  es  de  it
------------------------------------------------------------------
1       0g          499g        1.99    Y   Y   N   N   N   N   N
2       500g        999g        2.99    Y   Y   N   N   N   N   N
3       1000g       1999g       4.99    Y   Y   N   N   N   N   N
4       2000g       2999g       7.99    Y   Y   N   N   N   N   N
5       0g          499g        4.99    N   N   Y   Y   Y   Y   Y
6       500g        999g        6.99    N   N   Y   Y   Y   Y   Y
7       1000g       1999g       9.99    N   N   Y   Y   Y   Y   Y
8       2000g       2999g       14.99   N   N   Y   Y   Y   Y   Y

However the plan would be to add more and more country options. This would mean adding more columns each time. Is this the best way to structure this kind of data. Any other suggestions

Upvotes: 2

Views: 2946

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

It seems the rows of your table form groups of related pricing plans. If that's the case, I would suggest you have a PricingPlan table and a PlanDetail table. 

PricingPlan
-----------
PricingPlanId* PlanTitle
----------------------------------------
 1              planA for North America
 2              planB for EU

PlanDetail
----------
PricingPlanId* DetailId* FromWeight  ToWeight  Price
------------------------------------------------------
 1              1            0g        499g      1.99   
 1              2          500g        999g      2.99   
 1              3         1000g       1999g      4.99  
 1              4         2000g       2999g      7.99   
 2              1            0g        499g      4.99   
 2              2          500g        999g      6.99   
 2              3         1000g       1999g      9.99   
 2              4         2000g       2999g     14.99

A third, PlanCountry table should used so you don't have to add any columns for new countries that you want to relate to a pricing plan (a new row would be added in that table, if for example you want Mexico to be included in planA):

Country
-------
CountryCode* CountryName
-----------------------------
 us           USA
 ca           Canada
 uk           United Kingdom
 fr           France
 es           Spain
 de           Germany
 it           Italy

PlanCountry
-----------
PricingPlanId* CountryCode*
---------------------------
 1               us
 1               ca
 2               uk
 2               fr
 2               es
 2               de
 2               it
 1               mx                   --- Mexico added for planA

Upvotes: 0

bumperbox
bumperbox

Reputation: 10214

normally it is preferred practice to leave the table structure the same, and just add rows to cater for the case that you illustrated. (there are reasons such as optimisation where you can deviate from this)

I would suggest at looking up "3rd Normal Form", if your database complies with the rules to be in 3rd normal form then, you generally end up with a lot less maintenance and easier extendibility down the track.

table1
id | from_weight | to_weight | price   
1  | 0g          | 499g      | 1.99

table2
id | table1id | countrycode | status
1  | 1        | us          | Y
2  | 1        | ca          | Y
3  | 1        | gb          | N

this is how you would query the data

select price from table1 
join table2 on table1.id=table2.table1id
where countrycode='us' and status='Y' and
300 between from_weight and to_weight

Upvotes: 4

Dan
Dan

Reputation: 10786

Yes, I'd say so. Your goal is going to be to find the price for a given weight and country combination, so it should be easy to perform this query:

SELECT price FROM table WHERE $weight > from_weight AND $weight < to_weight AND $country;

And your schema allows that easily. I'd recommend that you select your to_weight such that a value of 499.9 fits in one of the categories, unless weight is restricted to be an integer. I wouldn't worry about adding new columns, that's easy and you can default it to false for any new country.

Upvotes: 0

Ignacio
Ignacio

Reputation: 5730

If you want to avoid adding columns, you can add a 2nd table that has priceID and Country as columns and just remove all the country columns. Then you just make priceID have a foreign key referencing the ID from your first table and you can add new countries as needed.

Upvotes: 0

Related Questions