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.
