Skip site navigation (1) Skip section navigation (2)

Re: Multiway associations

From: Michael Glaesemann <grzm(at)myrealbox(dot)com>
To: Russell Shaw <rjshaw(at)iprimus(dot)com(dot)au>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Multiway associations
Date: 2004-01-25 06:33:21
Message-ID: 5EDA84EA-4F00-11D8-A56D-000A95C88220@myrealbox.com (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

pgsql-novice by date

Next:From: Russell ShawDate: 2004-01-25 11:06:01
Subject: Primary key efficiency
Previous:From: Russell ShawDate: 2004-01-25 05:23:29
Subject: Re: Multiway associations

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group