From: Curtis Scheer <Curtis@DAYCOS.com>
To: pgsql-sql@postgresql.org
Subject: Table relationships
Date: Mon, 8 Jan 2007 15:39:31 -0600
Message-ID: <031936836C46D611BB1B00508BE7345D04DC1B78@gatekeeper.daycos.com> (raw)
I'm having trouble determining the best way to implement the following
scenario for a customer database.
Given the following tables what is the best way to link an address table to
both the "Master" and the "Detail" tables. Basically there can be many
addresses for each customermaster record and also many address for each
customerdetail record. Will this require two many-to-many tables? Or is
there a better solution I am missing? Is there any easy way to build in a
"Default" address or would that be something to do logically in client code?
Thanks,
Curtis
CREATE TABLE testing.customermaster
(
customermasterid int4 NOT NULL DEFAULT
nextval('testing.customermatser_customermasterid_seq'::regclass),
name varchar NOT NULL,
description varchar,
)
CREATE TABLE testing.customerdetail
(
customerdetailid int4 NOT NULL,
customermasterid int4 NOT NULL,
notes varchar,
closed bool,
customerdepartment varchar(3),
)
CREATE TABLE address
(
addressid int4,
name varchar(40),
addr varchar,
city varchar(42),
st varchar(2),
zip varchar(30)
)
Possible many-to-many tables
CREATE TABLE customermaster_address
(
addressid int4,
customermasterid int4
)
CREATE TABLE customerdetail _address
(
addressid int4,
customerdetailid int4
)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: Curtis@DAYCOS.com
Subject: Re: Table relationships
In-Reply-To: <031936836C46D611BB1B00508BE7345D04DC1B78@gatekeeper.daycos.com>
References: <031936836C46D611BB1B00508BE7345D04DC1B78@gatekeeper.daycos.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox