Received: from gl.aser.de (gl.aser.de [213.252.128.30]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5SEVka54328 for ; Thu, 28 Jun 2001 10:31:46 -0400 (EDT) (envelope-from gcg@gl.aser.de) Received: from pinguin.gl.aser.de (pec-160-113.tnt9.b2.uunet.de [149.225.160.113]) by gl.aser.de (Postfix) with ESMTP id 8184E21DE3; Thu, 28 Jun 2001 16:44:50 +0200 (MEST) Received: from pinguin (pinguin.gl.aser.de [192.168.1.111]) by pinguin.gl.aser.de (Postfix) with SMTP id 66A3D1385B; Thu, 28 Jun 2001 16:45:17 +0200 (CEST) Content-Type: text/plain; charset="iso-8859-1" From: Christof Glaser Organization: gl.aser software engineering . internet service To: "Christian Anton" , pgsql-sql@postgresql.org Subject: Re: Link many attributes in one table to one attribute in another?? Date: Thu, 28 Jun 2001 16:45:17 +0200 X-Mailer: KMail [version 1.2] References: <3b3ab3aa_1@newsa.ev1.net> In-Reply-To: <3b3ab3aa_1@newsa.ev1.net> MIME-Version: 1.0 Message-Id: <01062816451700.00740@pinguin> Content-Transfer-Encoding: 8bit X-Archive-Number: 200106/448 X-Sequence-Number: 3752 On Thursday, 28. June 2001 06:44, Christian Anton wrote: > Hello all, > > I am fairly new at db design, I have built a few simple in the past > and have never dealt with this type of problem before, any help would > be appreciated. > > I have three attributes in one table that should reference one, and > only one, attribute in another table. Here's an analogy of the > problem: > > I have a table with a list of marbles, each marble has three colors > in it (color1, color2, color3) and a serial number. In another table > I have eight colors to choose from but the list of colors grows > regularly. How do I associate a marble with three colors from the > color table (a marble may have three red sides or red-yellow-blue)? Quite easy: -- The list of colors. id is a automatically assigned unique number. -- You could use INT4 instead of SERIAL and make numbers of your own. CREATE TABLE color ( id SERIAL PRIMARY KEY, name text ); -- The list of marbles. The colors reference to the table color. -- Postgres makes sure that only existing colors are entered. CREATE TABLE marble ( number SERIAL PRIMARY KEY, color1 int4 REFERENCES color, color2 int4 REFERENCES color, color3 int4 REFERENCES color ); -- But you want to see the color's name rather its id, isn't it? CREATE VIEW v_marble AS SELECT m.number, c1.name as color1, c2.name as color2, c3.name as color3 FROM marble m, color c1, color c2, color c3 WHERE m.color1 = c1.id AND m.color2 = c2.id AND m.color3 = c3.id; Hope that helps. Christof -- gl.aser . software engineering . internet service http://gl.aser.de/ . Planckstraße 7 . D-39104 Magdeburg Tel. +49.391.7 44 77 10 . Fax +49.391.7 44 77 13 . Mobil 0177.77 92 84 3