Availability Calendar, best practices
Posted on 2005-05-11
I am looking a building an availability calendar system and would like a litle input first on the best way to approach it. Thanks ahead of time for your input.
I have users who each need a calendar to show their availability. Their availability is either unknown, available, or not available. When a user signs up They are defaulted to unknown and must update their calendar. I will need to display each user's calendar (easy) AND will also need to show all users available on specific dates (harder).
My question is about the best way to store this information in my DB. My first thought is to create a table for each month which would contain fields for (month, year, days) and store a string for the days like this (u,u,a,a,a,a,a,a,a,n,n,n,n,n,n,n,n,n,u,u,u,u,u,u,u,u,u,u,u,u). This would be easy enough to display a users entire calendar for several months at a time. Any pros or cons to this method?
The one stumbling block I am thinking about is how to do a query for all users available for a specific date or date range. I guess I could do a query for the specific month or months, load the results in an array and look for avaailability during those ranges. Is there an easier way?
I know somebody has built this before, so I hope I can learn from one of you with more experience than I. Thanks for any help or suggestions.