Calendar / Schedule DB Strategy

eddieangel

Good afternoon. I am working on an automated booking system, and I am trying to figure a strategy for resource availability as it pertains to data storage. The database has service providers in it (People who do things) and they are each available up to 24 hours a day 7 days a week. They might be available only on Monday from 1 to 2 AM. They need to have the ability to say they are available in different hour brackets. I am scratching my head a little bit thinking about the database design. I could have 169 columns in the table (1 for userid, and then 1 for each hour of the week), but that sounds laborious. I also considered a binary style string 168 characters long where each hour of the week has a specific string position (1000000100000111110000), but that is equally or more laborious. I also considered the idea of allowing the user to store their information in a table that tracks the day of the week, and the start/end time of their availability. The table could have multiple rows per day so if the user is available from 1 to 5 and then 6 to 10, he or she could have two Monday rows in the DB, but I am not sold on this strategy either. Anyways, does anyone have experience in this particular kind of data storage that they would like to share? Any and all thoughts are welcome. Cheers

David Mujica

You may want to consider 7 columns with a 24 byte array in each column. A blank indicated availability. My reason for this is that is would be easy to generate queries like "who is available on Mondays ?". select username from availTable where mondayAvail = "" Your design really depends on how you think you will be using (querying) the data. You may want to consider the ability to store a person's availability for a given time frame. Consider ... Joe is available Monday (9:00 - 17:00) for January, but after that he will switch to evenings (17:00 - 02:00). With that requirement, then I would go with an effective data in the front of the availability table which indicates FromThisDate ToThisDate on these days of week and these hours, the resource is available. Sorry I couldn't be more definitive. Good luck.

eddieangel

Quite insightful, thank you. In regards to the 24 bit array, when you say that a blank indicates availability how would you manage partial availability? Were you thinking the same sort of array as I suggested prior? (111100001111111111111111 indicates availability at all times aside from 5 to 9 AM)

Mycroft Holmes

Well I would say you can take your bits and ..... I would not even consider the bit type solution, purely from a downstream support aspect. Imagine coming in to support such a system. I think the table solution is going to be more flexible and supportable and will be more queriable by your users (who probably have no idea what a bit is). I would have 2 tables, Dates and Times, allow the user to enter as many times for a day as they require, supply a propagate forward by week/month methods. This can then interact with a calendar control.