Re: Multiway associations

On Jan 25, 2004, at 2:23 PM, Russell Shaw wrote:

> Michael Glaesemann wrote:
>> Hi Russel
>> On Jan 25, 2004, at 12:46 PM, Russell Shaw wrote:
>>> I have a list of parts, each of which is sold by multiple
>>> vendors. I also have a list of vendors, each of which sell
>>> multiple parts.
>>>
>>> How should i arrange the tables for this that doesn't involve
>>> having lots of empty fields "just in case" ?
>> One common way to do this is to have three tables: one suppliers, one
>> parts, and one suppliers-parts referencing suppliers and parts.
>
> Hi,
>
>> Does that help?
>
> Maybe so. I thought of this and was wondering if it was the common
> solution.
> Should it be something like:
>
> spid  supplier  part
> ---------------------
> 0     sid_1     pid_1
> 1     sid_1     pid_2
> 2     sid_2     pid_2
> 3     sid_3     pid_3
> 4     sid_3     pid_1
> ...
>
> Ie, the third table just stores all the combinations of parts and
> suppliers?

Yup. The spid might be superfluous, depending on what you need your
database for. I've never needed one. You're probably only going to be
doing queries like

SELECT part FROM suppliers_parts WHERE suppplier = sid_1

or variants of these. Probably will never touch the spid column.
(Unless of course you have a particular reason for doing so :)

Michael Glaesemann
grzm myrealbox com