Thread: Multiway associations
Hi, I'm making my first database. 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" ?
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. Does that help? Michael Glaesemann grzm myrealbox com
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?
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