# Thread: Design help needed: how to combine columns?

1. ## Design help needed: how to combine columns?

Suppose that I'm exporting my products and use several shipping companies. Each company gives me their rates and I want to consolidate all rates in my database. The problem is they have
different way of calculating rates. For example:

CompanyA - Weight based
Code:
```Origin	  Destination  1st 100gr  Next 50gr
LosAngeles  New York	 \$3.00	  \$0.50
Chicago	 New York	 \$1.50	  \$0.25
Seattle	 Washington   \$3.00	  \$0.30```
CompanyB - Volume based using standard boxes
Code:
```Origin	  Destination  Express? Small  Medium  Large   Jumbo
Los Angeles  New York	 Y		\$15.00 \$25.00  \$50.00  \$100.00
Los Angeles  New York	 N		\$ 7.50 \$14.00  \$20.00  \$50.00
Chicago	  New York			  \$ 4.00 \$ 6.50  \$10.00  \$16.00
Seattle	  Washington   Y		\$10.00 \$13.50  \$16.50  \$21.00
Seattle	  Washington   N		\$ 6.00 \$ 9.00  \$12.50  \$18.00```
CompanyC - Region based
Code:
```Origin   Destination  Max. weight  Rate
Region1  Region2	1kg	  \$ 8.00
Region1  Region2	5kg	  \$20.00
Region1  Region3	1kg	  \$13.00
Region2  Region2	1kg	  \$ 9.00```
Is there any way to combine these rates into one table? I don't want to create a table for every company (and I don't think it's a wise decision either). In the future, it's quite likely that I have new shipping companies with completely different rate rules. I've been thinking about this for weeks but still haven't got clue. Anyone can help?

2. yes, it's quite simple, really

make one table that has company, origin, and destination as a three-part composite primary key, together with all the other columns from all the various ways a rate can be calculated, allowing these columns all to be null, and finally, a column that indicates how the rate is calculated, say "calctype" or whatever

when additional companies are added with new formulas, just alter the table and add the necessary new columns

do the actual rate calculations based on each particular shipment in application logic, using the calctype column to determine which formula to apply

rudy

3. Thank's Rudy! That means I may have to add new columns to accomodate new vendors? But how do I set the column data type since for one vendor it may contain number but for others it's string?

4. add a new column if it's not exactly like an existing one