Re: setting up a table relationship

Le Sat, 26 Jun 2010 17:55:02 -0400,
Mag Gam <magawake@gmail.com> a écrit :

...
> 2010/06/24
> Task A:started at 3:30AM:ended 6:02AM
> Task B:started at 4:30AM:ended 7:04AM
> Task C:started at 8:45AM:ended 12:04PM
> Task D:started at 7:30AM:ended 11:01AM
>
> I would like to do metric such as, "On 2010/06/25 what was the longest
> task", "how does task B look for X number of days", etc..
>
> I setup 2 tables, task and t. Task is for the task and t is for the time.
>
> Now, I am getting confused on how to setup this relationship. This
> should be a many-to-many relationship. Can anyone give me advise on
> how to better organize this data?

?

CREATE TABLE task (id SERIAL PRIMARY KEY,
                   task  CHAR(1) NOT NULL);

CREATE TABLE time (id SERIAL PRIMARY KEY,
                   task INTEGER NOT NULL REFERENCES task(id),
                   start_end BOOLEAN NOT NULL, -- True==start
                   time TIMESTAMP NOT NULL);

if you have huge mining and don't want to perform date/time extraction from
a timestamp, you can separate this field in 2.

--
Maturity is only a short break in adolescence.
        -- Jules Feiffer