Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id B70C29FB644 for ; Tue, 31 Oct 2006 15:39:38 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 19416-01-4 for ; Tue, 31 Oct 2006 19:39:22 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.170]) by postgresql.org (Postfix) with ESMTP id AA0C29FB500 for ; Tue, 31 Oct 2006 15:33:01 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id 30so1349111ugc for ; Tue, 31 Oct 2006 11:33:00 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=K0YfcLLOczevBHsATw+4G7GV+3vKy1DIDN/p2hh5rPMt0IIJ7g2J/J01Z3QITMimqc34VDhMVGyUnNWHPeWPuLeGyhI/pinxDxx26wLYhBQ+g15nwKvqJps4i6hNcTd/rS/9qzWMRkxLaUZqWFL6WpluHImogNtP2qI7irhVCHE= Received: by 10.78.201.15 with SMTP id y15mr1807312huf; Tue, 31 Oct 2006 11:32:59 -0800 (PST) Received: by 10.78.136.6 with HTTP; Tue, 31 Oct 2006 11:32:59 -0800 (PST) Message-ID: Date: Tue, 31 Oct 2006 13:32:59 -0600 From: "Aaron Bono" To: "A. Kretschmer" Subject: Re: Table Relationships Cc: pgsql-sql@postgresql.org In-Reply-To: <20061031184843.GA12767@a-kretschmer.de> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_8163_1098759.1162323179633" References: <031936836C46D611BB1B00508BE7345D04C02E82@gatekeeper.daycos.com> <20061031184843.GA12767@a-kretschmer.de> X-Google-Sender-Auth: 9a5210d1ed809481 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200610/250 X-Sequence-Number: 26639 ------=_Part_8163_1098759.1162323179633 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 10/31/06, A. Kretschmer 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 ================================================================== ------=_Part_8163_1098759.1162323179633 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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
================================================================== ------=_Part_8163_1098759.1162323179633--