Table Design Issue

[Jeremy]

Hi,

I'm trying to design a database which includes Vendor
information, the Area a Vendor Services (list of states or
metropolitan ares w/in a state, for large states), and the
Region which each area belongs. End users want to be able
to report by region to see which Vendors Service an entire
region.

My question is whether I should have each Vendor tied to
the areas it services and then have each area associated
with the region. Or, should i have each vendor associated
with the complete regions it services and then each vendor
will be linked to the additional areas. Obviously, the
latter runs into problems if the Regional definitions
change or if the additional areas add up to complete a
region over time. But, the former method produces MANY
more records and seems to be quite difficult to write
queries from.

Am I missing something? Is there an easier way to do this
relationship?

Any help is appreciated. Thanks.
Jeremy

 

[Tim Ferguson]

I'm trying to design a database which includes Vendor
information, the Area a Vendor Services (list of states or
metropolitan ares w/in a state, for large states), and the
Region which each area belongs. End users want to be able
to report by region to see which Vendors Service an entire
region.

I guess you have three tables:

Vendors(*VendorID, etc...)

Areas(*ACode, Vendor, Region, etc...)

Regions(*RNumber, etc...)

with relationships enforced as follows:-
Areas.Vendor referencing Vendors.VendorID, and
Areas.Region referencing Regions.RNumber

This assumes that each vendor vends to many areas, but an area only has one
vendor. Now you can get the vendors who cover a region by joining all three
tables:

select vendors.* from
vendors left join
( areas left join regions on areas.region = regions.rnumber
) on vendors.vendorid = areas.vendor
where regions.description = "Wild West";

-- no I have not tested that, but it would be pretty easy to set up in the
query grid in any case. Functionally, there is a many-to-many relationship
between Regions and Vendors -- is this appropriate? If not, what rule
ensures that each Vendor stays within an area?

If an Area can be serviced by more than one Vendor, then the picture is
slightly more complicated, because you need a new table IsVendedBy(
*VendorID, *AreaCode) but it still just goes into the chain of joined
tables in the query designer.

Hope that helps


Tim F