I am looking at creating an online work schedule using the .net calendar control tied back to a SQL Server database (think Outlook Calendar). I am at a loss as to how to efficiently design the database. Will I need to create an entry for every day? Is it prudent to use a datetime value for a primary key (I would have to whack the time portion)? How does one handle the number of days in a month/year?
Anyhoo... I would greatly appreciate any ideas/resources on how to design a database of this type.
Thanks in advance.I can't see any reason to have an entry for every day. Why have an entry if no events are scheduled for a certain day? Just store the events in an event table with a date as one of the columns.|||I am also, have you had any luck?|||You probably have a solution to this, but I was tasked with creating a calendar to schedule meeting rooms as one of my first projects for my current employer.
My table calendar has the following design.
CalendarID, int, autoinc
calendarDateTime, datetime
Appointment, varchar, 50
Reservedby, varchar, 50
Duration, varchar, 50
Freetime, varchar, 50
RoomValues, varchar, 50
AppointmentTime, varchar, 50
Each appointment is it's own row. The asp.net pages check to see if a appointment exists at the time the user selects, if not, it checks the duration which is in 1/2 hour increments if there is an overlap it won't enter the appointment. Otherwise if the time is free it inserts the record. I then query all the appointments for the day and build a table to display the information in a graphical way, Green background for free time, and yellow for occupied. I only display from 8:00AM to 5:00PM on the webpage, but allow scheduling to be anytime.
I hope this helps. BTW, it has been working like a charm since the first week I started here back in March.
Greg
No comments:
Post a Comment