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==================================================================