td234
asked on
Availability Calendar, best practices
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
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.
Thom
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
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.
Thom
ASKER
HI Alan.
Seems like I did not provide enough details. Here are some comments and details.
1) I do have a table for users.
2) The user's status if for the entire day, not for time within the day.
What you are suggesting is having a different record for each day for each user. This seems like a huge amount of records to accomplish this task. Is this the best practice?
Seems like I did not provide enough details. Here are some comments and details.
1) I do have a table for users.
2) The user's status if for the entire day, not for time within the day.
What you are suggesting is having a different record for each day for each user. This seems like a huge amount of records to accomplish this task. Is this the best practice?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi, for easyer maintainance and stable system users should only enter dates that are "available" or "not available". Dates that are unknown should not be entered into database.
That way you don't have to worry for missing dates for users and database would be cleaner cause of less unneeded data.
You might have question how do I select data for "empty" dates... Well you just create LEFT JOIN.. and return "unknown" status for all dates in range that returns NULL.
hope that helps
cheers
That way you don't have to worry for missing dates for users and database would be cleaner cause of less unneeded data.
You might have question how do I select data for "empty" dates... Well you just create LEFT JOIN.. and return "unknown" status for all dates in range that returns NULL.
hope that helps
cheers
gruntar,
I believe I already stated that...
"You would only have a record for each day that a user is available or not available. No need to store a record for unknown, the absense of a record for a given use on a give day would signify unknown."
Alan
I believe I already stated that...
"You would only have a record for each day that a user is available or not available. No need to store a record for unknown, the absense of a record for a given use on a give day would signify unknown."
Alan
hmm, yes. well, i've read question and wrote down my own solution (why don't he need those unknown values and how would he select rows so that he would get all needed data).
cheers
p.s. you'll get your points if author thinks you gave him the "right" answer.
cheers
p.s. you'll get your points if author thinks you gave him the "right" answer.
It's not about points for me, you may have the points. I was just curious as to why you would simply restate what I had already said.
Alan
Alan
ASKER
Thanks Guntar. Good answer. I thought Alan's idea of a seperate table for each month did not sound like the best schema. I already have 43 tables in this DB and adding 12 jsut for one application seems like it would not be the best solution.
Thanks.
Thanks.
Ummmm, What? I told you NOT to use 12 tables and explained in detail why you shouldn't use 12 tables like YOU suggested you where going to do...
you said -> "My first thought is to create a table for each month"
Guntar literally repeated the exact thing I told you to do.
Wow.
Alan
you said -> "My first thought is to create a table for each month"
Guntar literally repeated the exact thing I told you to do.
Wow.
Alan
ASKER
Sorry, I misread Alan's comment about the 12 monthly tables. I misspoke (saying a table for each month) in my original question and when I saw him talking about 12 tables I thought that was his suggestion.
The other thing that threw me was Alan's suggestion of a StartTime and EndTime. From my original question I thought it was obvious that time was not a factor, just the status per date, so I was not sure he understood.
As I read this thread more carefully, I think Alan's original answer is more accurate thanI gave him credit for.
The other thing that threw me was Alan's suggestion of a StartTime and EndTime. From my original question I thought it was obvious that time was not a factor, just the status per date, so I was not sure he understood.
As I read this thread more carefully, I think Alan's original answer is more accurate thanI gave him credit for.
Thom,
Thank you for taking another look at this and accepting my answer. In reading through this post again I can now see where my comments where confusing and for that I apologize, I will be more careful when using quotations in the future.
Most importnatly though, did you get the information you where after? If not please say so and I will be happy to explain my thoughts in more detail.
Alan
Thank you for taking another look at this and accepting my answer. In reading through this post again I can now see where my comments where confusing and for that I apologize, I will be more careful when using quotations in the future.
Most importnatly though, did you get the information you where after? If not please say so and I will be happy to explain my thoughts in more detail.
Alan
ASKER
HI Alan.
Yes, I got exactly what I need and also found some sripts that do the same thing and store their data as we have dicussed. Those scripts have saved me several hours of programming and I have already costomized them for my needs. Thanks again.
Yes, I got exactly what I need and also found some sripts that do the same thing and store their data as we have dicussed. Those scripts have saved me several hours of programming and I have already costomized them for my needs. Thanks again.
User
=======
ID
firstname
lastname
etc...
Calendar
======
ID
UserID
Date
StartTime
EndTime
Status
This way each event(block of time) is represented as on record in a table. Because they are linked by UserID quereying all scheduled time for a given user is now quite simple.
Alan