Schedule design issue

Hi,
I need advice (being a newbie) on designing a schedule Availability
table.
I have a schedule table which I write availabilities to this way:
Based on if this is a one day event or a recurrent event, I am writing
to the DB every schedule day. i.e, if this availability starts on jan 01
and end march 01 every Mon,Tue,Wedn from 12:00 to 5:00PM, then I write a
row for every date (Which comes to approx 8 or 9). So imagine if someone
puts a recurrence for 120 ws!!
Is there another way to do this? I thought about changing it to this:
Taking the same example above, 1 row would be created with a new column
("recurrence" as the no. of ws) and a column for every day of the
w()..
I would like to know from the gurus out there what is the best solution
.I want to give the user the option to edit a recurrent event and
adding a recurrent column would give me this capability..
Thanks
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Patrick Delifer wrote:
> Hi,
> I need advice (being a newbie) on designing a schedule Availability
> table.
> I have a schedule table which I write availabilities to this way:
> Based on if this is a one day event or a recurrent event, I am writing
> to the DB every schedule day. i.e, if this availability starts on jan
> 01 and end march 01 every Mon,Tue,Wedn from 12:00 to 5:00PM, then I
> write a row for every date (Which comes to approx 8 or 9). So imagine
> if someone puts a recurrence for 120 ws!!
> Is there another way to do this? I thought about changing it to this:
> Taking the same example above, 1 row would be created with a new
> column ("recurrence" as the no. of ws) and a column for every day
> of the w()..
> I would like to know from the gurus out there what is the best
> solution .I want to give the user the option to edit a recurrent
> event and adding a recurrent column would give me this capability..
> Thanks
>
One way to implement is to have a schedule table with the main schedule
information (e.g. Pay Employees and start date / end date) and all the
related scheduling information (e.g. every 2 ws). In the calendar
table your recurring appointments should contain a reference to the main
schedule PK. I see no reason not to place actual rows in the appointment
table.
That way, like you do in Outlook, if you decide to edit a particular
appointment, you can edit the entire series or edit the specific item.
If you edit the specific item, it's no longer bound to the main
cecurring appointment.
David Gugick
Imceda Software
www.imceda.com|||>> I want to give the user the option to edit a recurrent event and
adding a recurrent column would give me this capability. <<
Well, not quite. Your Monday appointment just got bumped for
President's Day today! I would first build a Calendar table for the
next ten years or so, with all the holidays, etc.
Next, I would use one row per client appointment as you have proposed.
Inserting the appointments is a front end problem; modeling them is a
database problem.
The front end consults the calendar table to get a set of possible
dates for the recurring appointment, then consults the Appointments
table to see if there is an open slot. I would number the
appointments in a series and keep that data in a client table ( "Mr.
Celko needs 8 appointments, as close to each Monday as we can make
them").|||Thanks for the replys.
I'm not sure i's quite clear yet..But let me reiterate my problem with
more details:
The schedule table I write to contains events (which are availabilities
not appointments (not that it matters..but it gives a more indepth look
at the problem)..Based on these availabilities, users can make
appointments...But the appointments in this case are not important to
me. It's an event that occurs one time(very restrictive, no recureence
or anything like that.)
So I am not sure I understand your advice: Should a date be created for
every availability event? and this availability would contain a
recurrenceID which would allow it to be flagged as a recurrent item, and
then you would be able to edit a particular event within this
recurrence.?!..
If you look at Microsoft Outlook (or even MSN Calendar), when you create
a recurring appointment, you have the possibility of selecting "No end",
which means that this event has no end...And then it creates it in snap,
and the event recurrs indefinitely..I am certain that MS doesn't write a
row for every date the appointment is recurred.
If i understand your suggestions, you did say that a date should be
created for every event(So if i have an availability that recurrs
Mon,Tue,Wed for 4 motnhs, there would be approx 50 entries). But I'm
thinking about
having one row created per recurring event per day like so:
Columns:
ScheduleID (ID)
FromDate
ToDate
FromTime
ToTime
Day(int)
Ends(bool)
Recurrent(bool)
RecurrenceID
So for a the same example above, i would have 3 entries(Mon,Tue,Wedn)
with the start and end dates.
The problem with this design is that I still have an issue with editing
on item of the recurrence..
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!