Database Design

[Reed Stone]

I have been mulling over a problem for a while. How Do you support
recurring events in a database? there are a two category of options.
You can populate a database with discrete events based on a pattern or
store the pattern then calculate the events. The first option will
have a higher load on the creation of the events while the latter will
have a higher load when retrieving them.

I have decided on the latter as it would support "repeat forever"
events. I also wish to support "repeat on the first and third Friday
each month." I am unsure how to model this. I have come up with the
following after much exercising of my mind and Google.

events
---------------
id
title
description
start_date
end_date
start_time
end_time
repeat_type // null=no repeat; D=daily; W=Weekly; M =monthly;
(Yearly is supported by repeating every 12 months)
repeat_flag_0 //Daily, repeat every n days; Weekly, repeat on Sunday;
Monthly, repeat by date every n months;
repeat_flag_1 //weekly, repeat on Monday; monthly, repeat by weekday
every n months;
repeat_flag_2 //weekly, repeat on Tuesday; monthly repeating by
weekday, every first week
repeat_flag_3 //weekly, repeat on Wednesday; monthly repeating by
weekday, every second week
repeat_flag_4 //weekly, repeat on Thursday; monthly repeating by
weekday, every third week
repeat_flag_5 //weekly, repeat on Friday; monthly repeating by
weekday, every forth week
repeat_flag_6 //weekly, repeat on Saturday; monthly repeating by
weekday, every fifth week
repeat_end_date // repeat until date
repeat_max // repeat n times

Should I break all the "repeat" data to its own table? I think the
answer is no, but it keeps nagging me. Is there a cleaner way to do
this? There are some obvious performance Issues as all repeating
events would need to be quired and calculated. what would be the best
way to minimize the impact of supporting recurring events?

Thank you for your help in advance.

Reed Stone

[Tejay Cardon]

What is your context?  Are we talking about database events, or just a data model for an "event".  It sounds almost like a scheduled transfer in a banking application, or similar situation.  Do you need to be able to record any data for each discreat instance of the event?  What is the need of the database client.  ie. are we just doing a daily query to find out what events should be kicked off today, looking to build a calander of events, or something else?  The business logic for the access of data can impact your choices with regards to performance.  Do we expect this data to be queried daily, hourly, constantly, etc.  I'd need to know more to give much input, but I would agree with you that it seems there should be a better way.

 

Tejay

 

[wheaties.box]

Reed,

I too have struggled with this sort of concept before. I never really
came up with a "clean" way of doing it, but I usually got sidetracked
before I could invest much thought into it.

Despite my failures in the past, I agree with Tejay--more context
would be helpful for us to help you.

Josh

[Reed Stone]

Context

This is for calendar events that will be created, updated and deleted in a web app.  the calendar will be updated wiki style so there is potential for a some heavy database I/O.  there will also be a few many to many relationships with these tables and others I have not shown.  I was up past midnight thinking about it.  Here is what i came up with.

repeating_event
-----------------------

id
title
description
start_date
end_date

start_time // if null event is all day
end_time
repeat_type    // null=no repeat; D=daily; W=Weekly;  M=monthly; (Yearly is supported by repeating every 12 months)

repeat_flag_0  //Daily, repeat every n days; Weekly, repeat on Sunday; Monthly, repeat by date every n months
repeat_flag_1 //weekly, repeat on Monday;    monthly, repeat by weekday every n months

repeat_flag_2 //weekly, repeat on Tuesday;   monthly repeat by weekday, every first week
repeat_flag_3 //weekly, repeat on Wednesday; monthly repeat by weekday, every second week
repeat_flag_4 //weekly, repeat on Thursday;  monthly repeat by weekday, every third week
repeat_flag_5 //weekly, repeat on Friday;    monthly repeat by weekday, every forth week
repeat_flag_6 //weekly, repeat on Saturday;  monthly repeat by weekday, every fifth week

repeat_end_date // repeat until date
repeat_max // repeat n times

created_by
created_date

discrete_event
---------------
id
repeating_event_id //null if not a member of a recurring event
title
description
start_date
end_date
start_time // if null event is all day
end_time
created_by
created_date

repeating_event_calc (will only have one record)
---------------------
id
max_date

OK, when a non repeating event is created it is just added to the discrete_event table. If a repeating event is created a transaction is created where its metadata is recorded in the repeating_event table, its related discrete events are calculated and added to the discrete_event table up to the max_date value in the repeating_event_calc table.

When a query is processed The upper bounds of the date range is checked against repeating_event_calc's max_date value.  If the upper bounds of the query's date range is grater than max_date a transaction is created where the discrete_event table is updated to include recurring events up to the upper bounds date, then max_date is updated to indicate that all repeating events have been calculated up to this new date.

I may programmaticly limit a queries date range to be from the current date to current date + five years.  this will prevent a DOS attack by requesting I calculate the events until the year 9999999999999999.

Periodic archiving of old events will keep the database slimmer and performance up.  The above solution is also flexible so I can decide to switch from user initiated updates to scheduled updates for the discrete_event table if nessasary.  The above also has the ability to break an individual event from a set of recurring events.  This way if one event is started an hour late for some reason. it can be easily changed.

so let me know if you see any normalization issues that can be eliminated (or any other issues).

thanks,

Reed

[Tejay Cardon]

Try this on for size.

 

You have three tables (and possibly a table that holds calculated events.)

 

PatternTable

-PatternID

-RepeatFrequency  (1 daily, 2 weekly, 3 monthly)
-RepeatMultiplier    (1 - infinity)  This allows for daily, every 3 days.  Or Monthly every 3 months.

-Base Day             (-7 to 32)  negatives are the day of the week (sat = -7, sun = -1) and positives are day of the month.  0 starts with the start date of the event. 32 starts the last day of the month.

-Offset                   (integer) number of days between the base day and the event (see example for day of week offset)

-resetUnit               (null - never, 1 - daily, 2 - weekly , 3 - monthly, 4 - annual)  When the reset frequency has been reached, restart at the Next base day.

-reset frequency     (null, 1-inifinity)  how many reset units before a reset is triggered.

 

Offset Exmples:

if baseDay is a negative (day of week) then 0 is the week of the event start date.  -1 is the last <day of week> in the month. 1 is the first <day of week> in the month.

if baseDay is a positive (day of month) then offset is days + or - from that day of the month.

 

Example patterns:

Meeting every 3 days starting on the event start date

ID, 1, 3, 0, 0, null, null

 

Meeting every 5 days starting the first of the month

ID, 1, 5, 1, 0, null, null

 

One week before the end of each month

ID, 3, 1, 32, -7, null, null

 

The last Friday of each quarter

ID, 3, 3, -6, -1, null, null

 

Every 5 mondays in the year starting reset count every year

ID, 2, 5, -5, 0, 4, 1

 

Third Monday of each month

ID, 3, 1, -2, 3, null, null

 

Every 5 days starting on the 1st of the month reset each month (ie 1st, 6th, 11th, 16th, 21st, 26th, 31st)

This is different than every 5 days not reset.  The later would loop from Jan 31st to Feb 5th.

ID, 1, 5, 1, 0, 3, 1

 

EventTable

-EventID

-StartDate

-EndDate

-EventDetails

 

Event-PatternTable

-EventID

-PatternID

 

By using the Event-Pattern table, you can set multple patterns for the same event.  This would allow for a first and third Friday type pattern by creating a pattern for first, and one for third.  Also note, when calculating, you want to ignore days that do not exist.  For example, a pattern like this:

ID, 3, 1, -5, 5, null null.

This repeats the 5th friday of the month, every month.  If there is no fifth friday in a given month, then that month simply gets skipped over.

 

If you have any questions, let me know.

 

Thanks

 

Tejay

[Reed Stone]

WOW!  I like how compact you got the data.  I also like that the data is almost all Integers.  I do have two concerns.  First,  The logic of the database layout is not  as intuitive as the one I posted earlier.  This concern is offset by the fact that it is a more efficient  method of storage and supports a broad range of features.  Also after the database is abstracted away my concern can be called trivial (as long as the code is commented).

Second, If your UI were to take advantage of all your features it would present a nonstandard way of scheduling events.  Do you have any suggestions on how to over come this and make the user experence as axiomatic as possable.  I would not want Nelson Normon yelling at me!

Thx,

Reed

[Tejay Cardon]

I don't know that I would bother implementing the full feature set.  In my mind, the goal is to simply meet the customer need (business rule) with your UI.  Your database, on the other hand, needs to support as broad a feature set as possible.  That will help ensure that, if your business rules change, your database will still support your needs.  You want your database to be as flexible and extensible as possible.  So I would simply ask myself, "What types of events will my users want to be able to describe?"  At the most, I would provide a dual layer UI.  Think google search VS. advanced search.  The basic UI will allow standard style patterns.  The advanced feature will allow users to create patterns using advanced syntax that requries technically advanced users.  Ultimatly the question is, "Does anyone need this functionality?"

 

Good luck,

Tejay

 

[Reed Stone]

Well said. While I may never use the advanced features with my
current project. I never know if I may need to in a future
applacation. It would be nice to recycle some code and a database to
boot.

Thx,
Reed

On Aug 13, 8:49 am, "Tejay Cardon" <tejay...@gmail.com> wrote:
> I don't know that I would bother implementing the full feature set.  In my
> mind, the goal is to simply meet the customer need (business rule) with your
> UI.  Your database, on the other hand, needs to support as broad a feature
> set as possible.  That will help ensure that, if your business rules change,
> your database will still support your needs.  You want your database to be
> as flexible and extensible as possible.  So I would simply ask myself, "What
> types of events will my users want to be able to describe?"  At the most, I
> would provide a dual layer UI.  Think google search VS. advanced search.
> The basic UI will allow standard style patterns.  The advanced feature will
> allow users to create patterns using advanced syntax that requries
> technically advanced users.  Ultimatly the question is, "Does anyone need
> this functionality?"
>
> Good luck,
> Tejay
>

> On 8/8/08, Reed Stone <reedst...@gmail.com> wrote:
>
>
>
> > WOW!  I like how compact you got the data.  I also like that the data is
> > almost all Integers.  I do have two concerns.  First,  The logic of the
> > database layout is not  as intuitive as the one I posted earlier.  This
> > concern is offset by the fact that it is a more efficient  method of storage
> > and supports a broad range of features.  Also after the database is
> > abstracted away my concern can be called trivial (as long as the code is
> > commented).
>
> > Second, If your UI were to take advantage of all your features it would
> > present a nonstandard way of scheduling events.  Do you have any suggestions
> > on how to over come this and make the user experence as axiomatic as
> > possable.  I would not want Nelson Normon yelling at me!
>
> > Thx,
>
> > Reed
>

> ...
>
> read more »