Re: Table relationships

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

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

Not really an address is an address, it's a matter of specify an address for
the customer master record which basically represents an entire customer
while the customerdetail represents departments within that company that
might be at a different address then the company's main office for instance.

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

Many addresses can belong to many customer detail records. The
customermaster table should only contain one address so that should be a one
to many relationship. I guess the real problem is defining which address is
the "Main Office" for a given company. So maybe making different "Types" of
addresses and referencing them to the customerdetail table is the best way
to go? For instance

CREATE TABLE customerdetail _address

(

addressid int4,
customerdetailid int4
addresstypeid varchar

)

CREATE TABLE testing.addresstype

(

addresstypeid serial NOT NULL,

shortdescription varchar(15) NOT NULL,

description varchar(100),

CONSTRAINT pk_addresstype_shortdescription PRIMARY KEY (shortdescription)

)

The only other problem I see is if a particular customer has the same
address for all the departments in the company, then I guess the addressed
would exist multiple times but in the customerdetail_address table but the
user would only have to select that particular record rather then input the
same address again.

_____

From: Aaron Bono [mailto:postgresql(at)aranya(dot)com]
Sent: Monday, January 08, 2007 4:43 PM
To: pgsql-sql(at)postgresql(dot)org
Subject: Fwd: [SQL] Table relationships

---------- Forwarded message ----------
From: Aaron Bono <postgresql(at)aranya(dot)com <mailto:postgresql(at)aranya(dot)com> >
Date: Jan 8, 2007 4:42 PM
Subject: Re: [SQL] Table relationships
To: Curtis Scheer <Curtis(at)daycos(dot)com <mailto:Curtis(at)daycos(dot)com> >

On 1/8/07, Curtis Scheer <Curtis(at)daycos(dot)com <mailto:Curtis(at)daycos(dot)com> >
wrote:

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://www.aranya.com>
http://codeelixir.com <http://codeelixir.com>
==================================================================