Re: Table relationships - Mailing list pgsql-sql
| From | Curtis Scheer |
|---|---|
| Subject | Re: Table relationships |
| Date | |
| Msg-id | 031936836C46D611BB1B00508BE7345D04DC1BB5@gatekeeper.daycos.com Whole thread Raw |
| In response to | Table relationships (Curtis Scheer <Curtis@DAYCOS.com>) |
| Responses |
Re: Table relationships
|
| List | pgsql-sql |
->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@aranya.com]
Sent: Monday, January 08, 2007 4:43 PM
To: pgsql-sql@postgresql.org
Subject: Fwd: [SQL] Table relationships
---------- 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 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
==================================================================