DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding;
	b=RbOIdDJ/JVEAXmVBRlr5nLkpfTGilfpOQB+ZCCZJO5uY/QkCSiXn988GEW+5ejf0ixbjWi/Jc6U30zXBerMvLyObqhyPGex3yZU7E/oLKVSYQOO0tqWth5fntpnL7WkJJdoBDKgaHwr5bMY6KiZFWda8ehlpINMO7KPXTpWGjic=
Message-ID: <59b2d39b050102020525847461@mail.gmail.com>
Date: Sun, 2 Jan 2005 02:05:18 -0800
From: Miles Keaton <mileskeaton@gmail.com>
Reply-To: Miles Keaton <mileskeaton@gmail.com>
To: pgsql-sql@postgresql.org
Subject: one table being used for two purposes with foreign key for each?
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

What if, like Amazon, a customer can have more than one address on
file?  Then you make "addresses" a separate table with one-to-many
relationship.

So then you're making affiliates (or clients, or distributors) and you
realize it would be nice to re-use the fields you already have there
in the "addresses" table, even though the affiliate's address is
one-to-one, NOT a one-to-many.

Would it be bad design to have different foreign keys, as shown below?
 Is this a bad idea?

Should I just re-type the address field definitions directly into the
affiliates table, since it's not one-to-many?


create table customers (
id serial primary key,
name varchar(64)
);

create table affiliates (
id serial primary key,
name varchar(64)
);

create table addresses (
id serial primary key,
customer_id int REFERENCES customers(id),
affiliate_id int REFERENCES affiliates(id),
addr1 varchar(64),
addr2 varchar(64),
city varchar(64),
state varchar(12),
postalcode varchar(12),
country char(62),
CONSTRAINT needs_link CHECK (customer_id IS NOT NULL OR affiliate_id
IS NOT NULL)
);