From: | Fred Janon <fjanon(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Need advice on table structure for DATE & OVERLAPS |
Date: | 2009-08-19 11:02:52 |
Message-ID: | 2fd0c7810908190402l452fd7cag8c9dca5392cab5f6@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi,
I need to create a database to find events that have a start date and an end
date and fall between a certain start date and end date specified by the
user. I first designed my table where the start/end dates are part of the
events table, like this:
CREATE TABLE events
(
id serial NOT NULL,
title varchar,
startdate date NOT NULL,
enddate date NOT NULL,
CONSTRAINT pk_id PRIMARY KEY (id)
);
I was planning to use queries using OVERLAPS like this:
SELECT * FROM events WHERE (startdate, enddate) OVERLAPS (DATE
'2009-02-02',DATE '2009-01-03');
Now I wonder if it would be more efficient to have the table 'events'
pointing at another table 'times' containing the tuples (startdate, endate)
and doing the overlap on the 'times' table and finding the events that
points to the 'times'. That would allow re-use of the (startdate,enddate)
tuples.
Thanks
Fred
From | Date | Subject | |
---|---|---|---|
Next Message | Sean Davis | 2009-08-19 11:11:05 | Re: The efficient method to create trigger |
Previous Message | Sean Davis | 2009-08-19 11:02:38 | Re: Why PostgreSQL? |