Re: Schema check

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.com> wrote in message news:r45fpv030n3357k7ta1539a12uv4s9poad_at_4ax.com...
> 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
Received on Thu Oct 23 2003 - 16:30:17 CEST