Thread: Table relationships
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I’m having trouble determining the best way to implement the following scenario for a customer
database.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">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
customerdetailrecord. Will this require two many-to-many tables? Or is there a better solution I am missing? Is there
anyeasy way to build in a “Default” address or would that be something to do logically in client code?</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Thanks,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Curtis</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">CREATE TABLE testing.customermaster</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial">(</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> customermasterid int4 NOT NULL DEFAULT
nextval('testing.customermatser_customermasterid_seq'::regclass),</span></font><pclass="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial"> name varchar NOT NULL,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> description varchar,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">)</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">CREATE TABLE testing.customerdetail</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial">(</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> customerdetailid int4 NOT NULL,</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial"> customermasterid int4 NOT NULL,</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial"> notes varchar,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> closed bool,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> customerdepartment varchar(3),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">) </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">CREATE TABLE address</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">(</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> addressid int4,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> name varchar(40),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> addr varchar,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> city varchar(42),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> st varchar(2),</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> zip varchar(30)</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">) </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Possible many-to-many tables<br /><br /></span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial">CREATE TABLE customermaster_address</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial">(</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> addressid int4,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> customermasterid int4</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">) </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">CREATE TABLE customerdetail _address</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial">(</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> addressid int4,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> customerdetailid int4</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">) </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font></div>
From: Aaron Bono <postgresql@aranya.com>
Date: Jan 8, 2007 4:42 PM
Subject: Re: [SQL] Table relationships
To: Curtis Scheer <Curtis@daycos.com>
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
==================================================================
->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
==================================================================
On Mon, 8 Jan 2007 17:07:56 -0600 Curtis Scheer <Curtis@DAYCOS.com> wrote: > -> 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 be that you need another table. Sounds like you have something like; company <===> address <===> detail Now you just have to indicate which address is the primary or head office. You can do that with a bool in the address table. You can even add a unique, partial index to address to assure that you only have one head office per company. Another alternative is to add another table which just holds the address: company <===> department <===> detail ^ ^ | | \==> address <==/ Obviously the address attached to the company is the head office. For departments you can either duplicate the reference to the address table or have NULL indicate a default to the head office. There are many possibilities. Which one is best will depend on analysing your particular business model. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
On Mon, 8 Jan 2007 17:07:56 -0600
Curtis Scheer <Curtis@DAYCOS.com> wrote:
> -> 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 be that you need another table. Sounds like you have something
like;
company <===> address <===> detail
This approach implies that the address defines the relationship between a company and the detail (the other departments/offices). I cannot think of a business model that would use this though there probably are some...
Another alternative is to add another table which just holds the
address:
company <===> department <===> detail
^ ^
| |
\==> address <==/
I'm not sure what this relationship is for. It would appear that a department can have different addresses for different companies.
There are many possibilities. Which one is best will depend on
analysing your particular business model.
I agree whole heartedly. That is why I recommend starting with a logic structure before moving on to the physical.
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
On Tue, 9 Jan 2007 09:13:35 -0600 "Aaron Bono" <postgresql@aranya.com> wrote: > On 1/9/07, D'Arcy J.M. Cain <darcy@druid.net> wrote: > > company <===> address <===> detail > > This approach implies that the address defines the relationship between a > company and the detail (the other departments/offices). I cannot think of a > business model that would use this though there probably are some... Like the obvious one - companies have many places (departments) that things are shipped to and details are things that are shipped. > I'm not sure what this relationship is for. It would appear that a > department can have different addresses for different companies. Hmm. I guess I missed that. I'm just tossing out ideas here. Real, in depth analysis would require an invoicable relationship. :-) > There are many possibilities. Which one is best will depend on > > analysing your particular business model. > > I agree whole heartedly. That is why I recommend starting with a logic > structure before moving on to the physical. Absolutely. Don't even think about the tables until you have mapped out the business model. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.