Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 8E5B89FB1BC for ; Mon, 8 Jan 2007 18:43:04 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 98586-05-5 for ; Mon, 8 Jan 2007 18:42:58 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172]) by postgresql.org (Postfix) with ESMTP id D24BB9FB1CA for ; Mon, 8 Jan 2007 18:42:56 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so2964656ugf for ; Mon, 08 Jan 2007 14:42:55 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=sAivW17hyvPp+iLdfHVc2glr+sBF7BlXFI+DoueOBxsD09e1SvzjnBCvRD3F/DlFnt4MQV6dmS2j1ack8i2lNlCAt9eUikBZ+fp6362j+XU3EucQx/4gexWl0SR6lDccyTGGylJcmTfmTrBiTtP5+EHCKltmwmvPjGBrjK4AFB4= Received: by 10.78.157.8 with SMTP id f8mr4304984hue.1168296174952; Mon, 08 Jan 2007 14:42:54 -0800 (PST) Received: by 10.78.57.4 with HTTP; Mon, 8 Jan 2007 14:42:54 -0800 (PST) Message-ID: Date: Mon, 8 Jan 2007 16:42:54 -0600 From: "Aaron Bono" To: pgsql-sql@postgresql.org Subject: Fwd: Table relationships In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_57746_4588663.1168296174758" References: <031936836C46D611BB1B00508BE7345D04DC1B78@gatekeeper.daycos.com> X-Google-Sender-Auth: 5d9853b6c6382ced X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/17 X-Sequence-Number: 27169 ------=_Part_57746_4588663.1168296174758 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline ---------- Forwarded message ---------- From: Aaron Bono Date: Jan 8, 2007 4:42 PM Subject: Re: [SQL] Table relationships To: Curtis Scheer On 1/8/07, Curtis Scheer wrote: > 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 > > ) > > > I hope you left out the foreign keys for simplicity. Make sure they get into your database. To answer your questions, I think it prudent to ask a few to get a better understanding of the meaning of your tables: Is there a difference between an address for the customer detail and an address for the customer? Is there some kind of significance to attaching an address to the customer detail as opposed to the customer? Attaching the address to the detail gives it a customer by referencing through the detail. Could you add a specific address to multiple customer and/or customer detail records or is the address only assigned to one? What it gets down to is that you must start with the LOGICAL data model and ask yourself what are the meaning of the relationships and what relationships make sense before you get down to creating the PHYSICAL database. -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_57746_4588663.1168296174758 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline ---------- Forwarded message ----------
From: Aaron Bono <postgresql@aranya.com>
Date: Jan 8, 2007 4:42 PM
Subject: Re: [SQL] Table relationships
To: Curtis Scheer <Curtis@daycos.com>

On 1/8/07, Curtis Scheer <Curtis@daycos.com> wrote:

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

)

 


I hope you left out the foreign keys for simplicity.  Make sure they get into your database.

To answer your questions, I think it prudent to ask a few to get a better understanding of the meaning of your tables:

Is there a difference between an address for the customer detail and an address for the customer?

Is there some kind of significance to attaching an address to the customer detail as opposed to the customer?  Attaching the address to the detail gives it a customer by referencing through the detail.

Could you add a specific address to multiple customer and/or customer detail records or is the address only assigned to one?

What it gets down to is that you must start with the LOGICAL data model and ask yourself what are the meaning of the relationships and what relationships make sense before you get down to creating the PHYSICAL database.

--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
==================================================================

------=_Part_57746_4588663.1168296174758--