SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot oodie's Avatar
    Join Date
    Jul 2000
    Location
    Misty Mountain
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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. #3
    SitePoint Zealot oodie's Avatar
    Join Date
    Jul 2000
    Location
    Misty Mountain
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    add a new column if it's not exactly like an existing one

    see ALTER TABLE ADD COLUMN


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •