Thread: Table Relationships
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Given the following two tables:</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 public.task</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"> taskid int4 NOT NULL DEFAULT nextval('task_taskid_seq'::regclass),</span></font><p
class="MsoNormal"><fontface="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"> CONSTRAINT pk_taskid PRIMARY KEY (taskid)</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"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">public.users</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"> userid int4 NOT NULL,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> username varchar,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> CONSTRAINT pk_userid PRIMARY KEY (userid)</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"> </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">I want to record which user “performed the task” and which user “checked the task”, I’ve come up
witha few ideas on this but I would like to know what the correct way would be to implement this into my table
design.</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">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"> </span></font></div>
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
On 10/31/06, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
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
==================================================================
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
==================================================================
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
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
On 10/31/06, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
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
==================================================================
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
==================================================================
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> 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
==================================================================