Thread: setting up a table relationship

I have some data like this

2010/06/26
Task A:started at 5:30AM:ended 6:00AM
Task B:started at 5:30AM:ended 7:00AM
Task C:started at 5:45AM:ended 6:00AM
Task D:started at 7:30AM:ended 11:00AM

2010/06/25
Task A:started at 1:30AM:ended 6:00AM
Task B:started at 1:30AM:ended 7:00AM
Task C:started at 2:45AM:ended 6:00AM
Task D:started at 3:30AM:ended 11:00AM

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?

TIA

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

thanks for the quick answer.

I have something very similar. Instead, I have

CREATE TABLE time (id SERIAL PRIMARY KEY,
                   task INTEGER NOT NULL REFERENCES task(id),
                   s timestamptz NOT null,  --start
                   e timestamptz NOT null, --end
                   time TIMESTAMP NOT NULL --date );

But shouldn't time, s and e be unique because if I accidentally run
multiple inserts on the same day I would have duplicate copies of the
data? I would really like to avoid that.



On Sat, Jun 26, 2010 at 6:16 PM, Jean-Yves F. Barbier <12ukwn@gmail.com> wrote:
> 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
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>