pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTable Relationships
13+ messages / 4 participants
[nested] [flat]
* Table Relationships
@ 2006-10-31 17:15 Curtis Scheer <Curtis@DAYCOS.com>
0 siblings, 1 reply; 13+ messages in thread
From: Curtis Scheer @ 2006-10-31 17:15 UTC (permalink / raw)
To: pgsql-sql
Given the following two tables:
CREATE TABLE public.task
(
taskid int4 NOT NULL DEFAULT nextval('task_taskid_seq'::regclass),
description varchar,
CONSTRAINT pk_taskid PRIMARY KEY (taskid)
)
public.users
(
userid int4 NOT NULL,
username varchar,
CONSTRAINT pk_userid PRIMARY KEY (userid)
)
I want to record which user "performed the task" and which user "checked the
task", I've come up with a few ideas on this but I would like to know what
the correct way would be to implement this into my table design.
Thanks,
Curtis
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Table Relationships
@ 2006-10-31 18:48 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Curtis Scheer <Curtis@DAYCOS.com>
0 siblings, 1 reply; 13+ messages in thread
From: A. Kretschmer @ 2006-10-31 18:48 UTC (permalink / raw)
To: pgsql-sql
am Tue, dem 31.10.2006, um 11:15:26 -0600 mailte Curtis Scheer folgendes:
> Given the following two tables:
>
> CREATE TABLE public.task
> (
> taskid int4 NOT NULL DEFAULT nextval('task_taskid_seq'::regclass),
> description varchar,
> CONSTRAINT pk_taskid PRIMARY KEY (taskid)
> )
>
> public.users
> (
> userid int4 NOT NULL,
> username varchar,
> CONSTRAINT pk_userid PRIMARY KEY (userid)
> )
>
> I want to record which user ?performed the task? and which user ?checked the
> task?, I?ve come up with a few ideas on this but I would like to know what the
> correct way would be to implement this into my table design.
Perhaps a table like this:
(
user int references public.users,
task int references public.task,
ts timestamptz default now(),
action char(1) check (action in ('p','c'))
)
-- with p(perform), c(cheked)
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Table Relationships
@ 2006-10-31 19:32 Aaron Bono <postgresql@aranya.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 13+ messages in thread
From: Aaron Bono @ 2006-10-31 19:32 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
On 10/31/06, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
>
> am Tue, dem 31.10.2006, um 11:15:26 -0600 mailte Curtis Scheer folgendes:
> > Given the following two tables:
> >
> > CREATE TABLE public.task
> > (
> > taskid int4 NOT NULL DEFAULT nextval('task_taskid_seq'::regclass),
> > description varchar,
> > CONSTRAINT pk_taskid PRIMARY KEY (taskid)
> > )
> >
> > public.users
> > (
> > userid int4 NOT NULL,
> > username varchar,
> > CONSTRAINT pk_userid PRIMARY KEY (userid)
> > )
> >
> > I want to record which user ?performed the task? and which user ?checked
> the
> > task?, I?ve come up with a few ideas on this but I would like to know
> what the
> > correct way would be to implement this into my table design.
>
> Perhaps a table like this:
>
>
> (
> user int references public.users,
> task int references public.task,
> ts timestamptz default now(),
> action char(1) check (action in ('p','c'))
> )
>
>
> -- with p(perform), c(cheked)
I would go further by adding a type table like this:
operation_type (
operation_type_id bigserial (PK),
operation_cd varchar(10),
operation_name varchar(20)
)
with two codes "perform" and "check" and another table
operation (
user int references public.users (PK),
task int references public.task (PK),
ts timestamptz default now() (PK),
operation_type_id bigint references operation_type
)
This gives you the flexibility to add more operation types in the future.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Table Relationships
@ 2006-10-31 20:08 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Aaron Bono <postgresql@aranya.com>
0 siblings, 1 reply; 13+ messages in thread
From: A. Kretschmer @ 2006-10-31 20:08 UTC (permalink / raw)
To: pgsql-sql
am Tue, dem 31.10.2006, um 13:32:59 -0600 mailte Aaron Bono folgendes:
> I would go further by adding a type table like this:
>
> operation_type (
> operation_type_id bigserial (PK),
You are sure, that you need bigserial?
> This gives you the flexibility to add more operation types in the future.
Yeah! 9223372036854775807 possible types, great! ;-)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Table Relationships
@ 2006-10-31 20:13 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 13+ messages in thread
From: A. Kretschmer @ 2006-10-31 20:13 UTC (permalink / raw)
To: pgsql-sql
am Tue, dem 31.10.2006, um 21:08:24 +0100 mailte A. Kretschmer folgendes:
> am Tue, dem 31.10.2006, um 13:32:59 -0600 mailte Aaron Bono folgendes:
> > I would go further by adding a type table like this:
> >
> > operation_type (
> > operation_type_id bigserial (PK),
>
> You are sure, that you need bigserial?
Hey, your idea is okay, but i think, we don't need *BIG*serial for this.
Okay?
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Table Relationships
@ 2006-10-31 20:43 Aaron Bono <postgresql@aranya.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 0 replies; 13+ messages in thread
From: Aaron Bono @ 2006-10-31 20:43 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
On 10/31/06, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
>
> am Tue, dem 31.10.2006, um 21:08:24 +0100 mailte A. Kretschmer folgendes:
> > am Tue, dem 31.10.2006, um 13:32:59 -0600 mailte Aaron Bono folgendes:
> > > I would go further by adding a type table like this:
> > >
> > > operation_type (
> > > operation_type_id bigserial (PK),
> >
> > You are sure, that you need bigserial?
>
> Hey, your idea is okay, but i think, we don't need *BIG*serial for this.
> Okay?
>
>
> Andreas
Sorry, just force of habbit. Serial works or you can just drop the id and
use the code as the primary key. You should at the very least put a unique
constraint on the code field.
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Table Relationships
@ 2006-10-31 22:04 Curtis Scheer <Curtis@DAYCOS.com>
0 siblings, 0 replies; 13+ messages in thread
From: Curtis Scheer @ 2006-10-31 22:04 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; A. Kretschmer <andreas.kretschmer@schollglas.com>; +Cc: pgsql-sql
Thanks for the help.
_____
From: Aaron Bono [mailto:postgresql@aranya.com]
Sent: Tuesday, October 31, 2006 2:44 PM
To: A. Kretschmer
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Table Relationships
On 10/31/06, A. Kretschmer <andreas.kretschmer@schollglas.com
<mailto:andreas.kretschmer@schollglas.com> > wrote:
am Tue, dem 31.10.2006, um 21:08:24 +0100 mailte A. Kretschmer folgendes:
> am Tue, dem 31.10.2006, um 13:32:59 -0600 mailte Aaron Bono folgendes:
> > I would go further by adding a type table like this:
> >
> > operation_type (
> > operation_type_id bigserial (PK),
>
> You are sure, that you need bigserial?
Hey, your idea is okay, but i think, we don't need *BIG*serial for this.
Okay?
Andreas
Sorry, just force of habbit. Serial works or you can just drop the id and
use the code as the primary key. You should at the very least put a unique
constraint on the code field.
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com <http://www.aranya.com;
http://codeelixir.com <http://codeelixir.com;
==================================================================
^ permalink raw reply [nested|flat] 13+ messages in thread
* Table relationships
@ 2007-01-08 21:39 Curtis Scheer <Curtis@DAYCOS.com>
0 siblings, 1 reply; 13+ messages in thread
From: Curtis Scheer @ 2007-01-08 21:39 UTC (permalink / raw)
To: pgsql-sql
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
)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Fwd: Table relationships
@ 2007-01-08 22:42 Aaron Bono <postgresql@aranya.com>
parent: Curtis Scheer <Curtis@DAYCOS.com>
0 siblings, 0 replies; 13+ messages in thread
From: Aaron Bono @ 2007-01-08 22:42 UTC (permalink / raw)
To: pgsql-sql
---------- 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
==================================================================
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Table relationships
@ 2007-01-08 23:07 Curtis Scheer <Curtis@DAYCOS.com>
0 siblings, 1 reply; 13+ messages in thread
From: Curtis Scheer @ 2007-01-08 23:07 UTC (permalink / raw)
To: pgsql-sql; +Cc: Aaron Bono <postgresql@aranya.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 <mailto:postgresql@aranya.com> >
Date: Jan 8, 2007 4:42 PM
Subject: Re: [SQL] Table relationships
To: Curtis Scheer <Curtis@daycos.com <mailto:Curtis@daycos.com> >
On 1/8/07, Curtis Scheer <Curtis@daycos.com <mailto: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://www.aranya.com;
http://codeelixir.com <http://codeelixir.com;
==================================================================
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Table relationships
@ 2007-01-09 14:41 D'Arcy J.M. Cain <darcy@druid.net>
parent: Curtis Scheer <Curtis@DAYCOS.com>
0 siblings, 1 reply; 13+ messages in thread
From: D'Arcy J.M. Cain @ 2007-01-09 14:41 UTC (permalink / raw)
To: Curtis Scheer <Curtis@DAYCOS.com>; +Cc: pgsql-sql; postgresql@aranya.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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Table relationships
@ 2007-01-09 15:13 Aaron Bono <postgresql@aranya.com>
parent: D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 1 reply; 13+ messages in thread
From: Aaron Bono @ 2007-01-09 15:13 UTC (permalink / raw)
To: D'Arcy J.M. Cain <darcy@druid.net>; +Cc: Curtis Scheer <Curtis@daycos.com>; pgsql-sql
On 1/9/07, D'Arcy J.M. Cain <darcy@druid.net> wrote:
>
> 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
==================================================================
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Table relationships
@ 2007-01-09 15:30 D'Arcy J.M. Cain <darcy@druid.net>
parent: Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 13+ messages in thread
From: D'Arcy J.M. Cain @ 2007-01-09 15:30 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: Curtis@daycos.com; pgsql-sql
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2007-01-09 15:30 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-31 17:15 Table Relationships Curtis Scheer <Curtis@DAYCOS.com>
2006-10-31 18:48 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-10-31 19:32 ` Aaron Bono <postgresql@aranya.com>
2006-10-31 20:08 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-10-31 20:13 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-10-31 20:43 ` Aaron Bono <postgresql@aranya.com>
2006-10-31 22:04 Re: Table Relationships Curtis Scheer <Curtis@DAYCOS.com>
2007-01-08 21:39 Table relationships Curtis Scheer <Curtis@DAYCOS.com>
2007-01-08 22:42 ` Fwd: Table relationships Aaron Bono <postgresql@aranya.com>
2007-01-08 23:07 Re: Table relationships Curtis Scheer <Curtis@DAYCOS.com>
2007-01-09 14:41 ` Re: Table relationships D'Arcy J.M. Cain <darcy@druid.net>
2007-01-09 15:13 ` Re: Table relationships Aaron Bono <postgresql@aranya.com>
2007-01-09 15:30 ` Re: Table relationships D'Arcy J.M. Cain <darcy@druid.net>
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