?
Solved

Availability Calendar, best practices

Posted on 2005-05-11
14
Medium Priority
?
400 Views
Last Modified: 2013-12-12
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.

Thom
0
Comment
Question by:td234
  • 6
  • 4
  • 2
12 Comments
 
LVL 9

Expert Comment

by:AlanJDM
ID: 13980218
I would do this with 2 tables, one for users and one for calandar.

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

0
 
LVL 2

Author Comment

by:td234
ID: 13980514
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?
0
 
LVL 9

Accepted Solution

by:
AlanJDM earned 1500 total points
ID: 13980682
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.

"create a table for each month"

What does this buy you? All this does is give you 12 tables to maintain instead of just one. 12 tables with 100 records each is no different than 1 table with 1200 records in terms of space. Unless you are talking about hundreds of thousands of records it wont make any noticible difference if speed either. Also, I'm curious why you would store "month" in a 'monthly" table? You would know the month for all records in the "May" table would be "May", right? Why then would you have a field in the table that contains the value of "May"?

"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)"

IMHO, this is the real flaw in your plan. The ammount of code, and thus execution time, that will be needed to parse and then rebuild this string each time a change is made is going to make this process exponentially more intensive then it would be by simply querying and updating a single record from a single table.


Alan


0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 9

Expert Comment

by:gruntar
ID: 13984080
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
0
 
LVL 9

Expert Comment

by:AlanJDM
ID: 13985181
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

0
 
LVL 9

Expert Comment

by:gruntar
ID: 13985894
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.

0
 
LVL 9

Expert Comment

by:AlanJDM
ID: 13985947
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
0
 
LVL 2

Author Comment

by:td234
ID: 13986463
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.
0
 
LVL 9

Expert Comment

by:AlanJDM
ID: 13986562
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
0
 
LVL 2

Author Comment

by:td234
ID: 13988394
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.
0
 
LVL 9

Expert Comment

by:AlanJDM
ID: 13989311
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
0
 
LVL 2

Author Comment

by:td234
ID: 13990016
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month16 days, 19 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question