Re: Need help/advice for database structure

I know that this is a "theoretical" newsgroup, but you have stated your problem in "real-world" terms, and in those terms, my experience in over 20 years of handling systems that had "shipping/postal vendor" components is that it is impossible to have a single table handle all requirements. Much as you hate to do it, you go with individual tables for each carrier.

HTH JL

v_rs_at_yahoo.com (Oodie) wrote in message news:<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 - 14:29:56 CET