Database Design Question

[dhstein]

I have a product table which lists all products and a vendor table which
lists all vendors. I have a field in the product table called vendorID
which relates the product to the appropriate vendor. The problem is I'd like
another field in the product table called AlternateVendorID for an
alternative vendor in case the first supplier is out of stock. But when I
try to relate that second field to the vendor table, none of my queries work.
Can I relate both of these fields to the vendor table? What am I doing
wrong? Thanks for any help.

 

[Douglas J. Steele]

In a query, you'd need to add the vendor table twice. (When you add it a
second time, Access will add a _1 to the name).

Link vendorID to one copy of the vendor table, and AlternateVendorID to the
other copy of the vendor table.

 

[Paul Shapiro]

While you're making changes you might consider a better design alternative.
Instead of vendorID and vendor2ID in the Product table, use an association
table ProductVendor, which might have fields productID, vendorID,
priorityRank, price, deliveryDays, etc. That makes queries more
straightforward since it's a single join to each of the two parent tables
(Product and Vendor) and the data is always in the same fields. It allows
storing more data about each product-vendor combination. It allows for the
future when someone wants to store a 3rd or 4th or 99th vendor for the same
product.

 

[David H]

Basically what's already been stated in the posts above. If you do add a
third table to relate Products to Vendors such as tblProductVendors, I would
certainly add a field that indicates if the Vendor is the PRIMARY or
SECONDARY vendor for the product so that you can identify from whom
replacements should be ordered. It comes down to a means by which you can
classify the vendors so that you don't go to the vendor of last resort before
going through others.
By placing the status in the tblProductVendors table, it will also allow for
situations where Vendor A is the primary vendor for a product, but a
secondary vendor for another.