Need help/advice for database structure

From: Oodie <v_rs_at_yahoo.com>
Date: 2 Mar 2003 22:02:46 -0800
Message-ID: <b2dda4bd.0303022202.14306add_at_posting.google.com>



I need to build a system to manage some shipping/postal vendors. As a (simplified) example, they all put their rates based on type of item shipped, origin, and destination:

TABLE rate (
  vendor_id
  commodity
  origin
  destination
  extra_charge
  rate
  (... more columns ...)
)

The problem is, although they have the same nature of business, their operations can be very different one another. One vendor has a list of 1000+ item/commodity types. Another will calculate their rate based on commodity *groups* rather than individual item. Yet other has a different list of items. Sometimes they even have exceptions for this.

For origin/destination, some use their own city/country codes, some use regions, some use their custom location grouping, etc. Or they may have 1 base rate from point A to B and calculate other rates in the region using formulas.

And for extra_charge, they all have different exception/rules and can be quite complex to put in only 1 separate table.

I've seen other applications create one set of tables which is customized for every vendor. I don't think this is a wise solution but can't think of better way of doing this especially when we need to add new vendor. I feel that this is the same as mapping each vendor business, and it's a humongous task. Can anyone help me please? Thank's Received on Mon Mar 03 2003 - 07:02:46 CET

Original text of this message