Ive just been chatting with a guy who has proposed a different
solution to a database design I'm working on.

The segment of the db is to provide a lookup of different customer and
supplier part numbers in comparison to "ours". So customer X uses par
t no xyz, cust Y uses abc, supplier A uses 123 and we use 666, all for
exactly the same part.

I had in mind -

customers, customers_partNum, suppliers, suppliers_partNum, partNum

but i'm not entirely happy with this design as suppliers can become
customers, etc...

It was proposed to me that I use a vendor table with a lookup table as
to whether they are a customer or supplier, and then programmatically
discern the customer/supplier details dependent on the results of the
lookup code and vendor ID. This design seems more code reliant rather
than allowing the SQL to do the work.

partNum, vendorPartNum (includes 'Type' lookup code and
customer/supplier ID), vendorType (lookup table for vendor 'Type'),
customers, suppliers

If anyone has any thoughts on this, I'd be pleased to hear them.

Ta

In most logistics applications that I am familiar with, a single vendor may
have multiple parts that correspond to one of 'our' parts and vice versa.
In this case, you have a pretty standard many-to-many situation and the
standard solution is to create a 'join table'. So you have the table for
'our' parts, containing 'our' part number; the table for 'their' parts
containing partNum and information about the vendor, then you have the join
table with 'our' parNum, 'their' parNum, and a code indicating if they are a
vendor, a supplier, or both for this particular part.
"Jo Brown" <jbrown666 (AT) hotmail (DOT) com> wrote