Need advice on table structure for DATE & OVERLAPS

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

Browse pgsql-novice by date

  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?