Re: Need help/advice for database structure

Oodie,

  My first impression is to keep the vendors' rates in separate tables. While "rate" is a reasonable entity to use in the model, with attributes <origination>, <termination>, <item_category> and so on.

Then if you want to rate something for each carrier, you may need to join on zipcodes for one vendor, through a region lookup table for another, etc.

I've handled similar situations this way and also by using a single table where one location column can hold a state, a zip code, or something else, depending on the rate_basis, but I sense the differences are more substantial here.

It's tricky if you want it to be fast. If you can live with slow, you can write all the special conditions as functions or procedures, which may mean much processing is done row by row.

Steve Kass
Drew University

Oodie wrote:

>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 - 08:41:43 CET