Page 1 of 1

How should I handle this database structure?

Posted: Fri Mar 16, 2007 11:00 am
by Begby
I need to setup a way to do discounts for ship methods. So if the method is second day air, and the package weighs 10lbs, the discount will either be a percentage, a certain fixed amount off the cost in dollars, or a flat rate. A discount structure might look like the following, considering a maximum weight of 170lbs


DHL Zone 2 Ground - Some client
1lb to 10lbs 20% off
11lbs to 30lbs $1.50 off
30lbs to 60lbs 25% off
60lbs + $68.95 flat rate

For another client the same ship type might be flat rate per pound like

DHL Zone 2 Ground - Some other client

1lb $1.50
2lb $1.65
3lb $1.70
4lb $1.83
5lb $1.99
6lb $2.05
7lb $2.10
....
170lb+ $58.50


There are hundreds of clients and all will be setup different, here is the raw data as I see it laid out in 1 possible table structure. In this one if the weight was 1lb, it would look first occuring discount entry and use that value.

ClientID, ShipType, Weight, Value, Discount type

So for the first client I might have
10 - DHL2 - 10 - 20 - PERCENTAGE
10- DHL2 - 30 - 1.5 - FIXED
10 - DHL2 - 60 - 25 - PERCENTAGE
10 - DHL2 - 170 - 68.95 - FLATRATE


Here are the 3 ways I thought of to do this

1. Have a table structure as above. However, given that there are about 60 ship types, and some clients have a flat rate for every weight (170 possible weights) within those ship types, we are talking about pulling 170 records to look up a discounts for a single ship types and to get the entire rate table fetching 10,200 records. Thats a lot of records.

2. Having the discount table setup with a field for every weight and a discount type field for every weight. This would be 3rd normal form but have a lot of null values. This means only fetching 1 record for a given package and getting 60 records to lookup an entire rate table. However, each record would have 340 columns. Thats a lot of columns.

3. Having a table with a serialized value for the discount structure possibly in XML or something else. This would mean 1 record for a given ship type and 60 records for a entire rate table, but then I would have the overhead of deserializing the data. If I went this route what would be the best way to serialize it keeping in mind I would want to access the data from PHP and also other languages like C#.

I am leaning towards method 2, anyone have any input on this?

Notes:

I will never need to search for a discount, for instance find all the clients with a discount < $1 for DHL zone 3.

There is a maximum of 170lbs, so a shipment type will never need more than 170 discount entries.

We may add or drop shipment types in the future.