From: | Miles Keaton <mileskeaton(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | one table being used for two purposes with foreign key for each? |
Date: | 2005-01-02 10:05:18 |
Message-ID: | 59b2d39b050102020525847461@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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)
);
From | Date | Subject | |
---|---|---|---|
Next Message | Karel Zak | 2005-01-02 16:19:23 | Re: Formatting an Interval |
Previous Message | TJ O'Donnell | 2005-01-01 19:20:36 | Re: Function in C++ |