help modeling a schedule system

From: chris h <chris404(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: help modeling a schedule system
Date: 2010-10-05 21:09:45
Message-ID: AANLkTinazJ_aiYbpe0CbkhsbZ11rhSNR48Cd3WvkKNq0@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm not sure if this is the correct list for this, but I'm hoping to get
some help with a data model I'm struggling with. Let me know if this should
be elsewhere! :)

We have an event schedule system that basically tells an event handler when
an event should be fired, and keeps a history of fired events. An event
would only be fired once per day, and can have the following settings.

a) Time of day (granularity of minutes, we're not worried about seconds):
e.g. 09:34, 17:00, etc.

b) Day of week (an event can be fired or not fired any/all days of the
week):
e.g. sun-sat; mon-fri; mon,wed,thr; mon; etc.

c) Ratio in fired-event-days to all-event-days (an event may only fire on
some of the days that it is suppose to):
e.g. 1:2 - would fire every other day, 1:1 would fire every day, etc.

d) Exceptions (events can have periods of set inactivity):
e.g. 20110201-20110210 would disable the event from February 1st, 2011
through February 10th, 2011, after which it would resume as normal.

note: we are expecting number of events to be in the 10's of thousands, but
I would like a solution that can easily scale into the few millions.

The models I've come up with to satisfy these requirements seem bloated and
overly complex to me. Having these events fire, on-time, is the biggest
priority here. By having a complex, temporal system the probability of
logic errors seems to be high; I want to avoid that.

Thanks for any help everyone!

Chris H.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Magnus Hagander 2010-10-05 21:10:33 Re: querying the version of libpq
Previous Message Tim Uckun 2010-10-05 21:06:11 Re: Trying to figure out why these queries are so slow