I am programming another website for the company I work for. This one is going to use both UPS and the United States Postal Service for shipments. We have only one product right now, but I need to program it to be scalable so when we do add additional products we can just plug in the products.

I would like your advice on the best way to programically structure the ordering system.

Here are my thoughts.
Since the product is a 4oz glass bottle with a high quality glass dropper, we have to package these in boxes. We are going to only allow product quanities of: 1,2,3,6 and 12. We figured out that 1-3 quanities will all fit in one box (4" x 4" x 6") and if they get 6 or 12 it will fit in another box (12" x 6" x 8"). My problem with this is that if I hard code it to do these, then if we add additional products I would have to take the website offline to make it work. Therefore here is how I see it in my mind:

Make the database that stores each product have a 'dot notation' type field, like libraries use:

qty|length|width|height|weight
Example:1|4|4|6|11.9 # Quantity of 1, box: 4x4x6 and weighs 11.9oz.
so I could make a text field that can store all the different values for that product. or I guess I can make another table that will have the product code so it is tied to that product, then have it structured this way:

CREATE TABLE product_quantities (
 pqid  INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
 pcode BIGINT UNSIGNED,
 qty   INT UNSIGNED,
 length DECIMAL(3,2),
 width  DECIMAL(3,2),
 height DECIMAL(3,2),
 weight DECIMAL(3,2)
);
[download]

Then foreach different quantity that a product code is allowed to have it will show the size of the box and the total weight of that quantity, that way I can pass it to UPS and USPS to get the shipment prices to give them a choice.

The big problem I have with this, is if we do add additional products and they purchase say 6 bottles of the real water, then 2 of whatever else we have, how would I be able to get my programming to figure out what size box that would combine to require? Do you know of a way to do it?

Also, I found a bunch of shipping modules for UPS and USPS resepectivly. It is crazy however, that I tested how much the shipment would be with Net::UPS for this: 4x4x6 box, weighing 1LB 12OZ. and from the shipping center in Oregon, to my house in Oklahoma. It came up with 17.99 and when I logged on to UPS to our account and did the same shipment it came up with 13.99 I could not figure our why such a difference.

So do you know how companies like TigerDirect, and many others get accurate prices to show in a drop down for the customer to select from?

I would appreciate any feed back on this too.

Thank you,
Richard