CODEWITHSUNDEEP
c#asp.netsql-server-2008
DarknessBeginsHere
DarknessBeginsHere

Reputation: 602

How to store weekly time availability in database?

I am programming an appointment system for doctors office. I need to store weekly availability of doctor. Something like this:-

        DoctorID | 10AM | 11AM |     12AM  | 1PM | 2PM |    3PM    | 4PM | 
        ------------------------------------------------------------------
 [MON]  5477J    |  1   |  0   | AppointID |   1 |   1 | AppointID | 0   |
        ------------------------------------------------------------------
 [TUE]  5477J    |  0   |  1   | AppointID |   1 |   1 | AppointID | 0   | 
        ------------------------------------------------------------------

I am storing the time slots as numeric 1 implies **avaialble** 0 implies **will not be in office** and if there is an appointment booked then the timeslot will be replaced by AppointmentID.

The availability is going to differ each day. My question is How do I store for availability for each day? Should I have 5 rows per weekly schedule. Can someone point me to some simple schema or is there a better way to do this.

Upvotes: 1

Views: 2560

Answers (1)

p.s.w.g
p.s.w.g

Reputation: 149020

One model I've used in the past is something like this:

TABLE [AppointmentBlock]
  - [DoctorID] INT
  - [StartTime] DATETIME
  - [EndTime] DATETIME
  - [IsAvailable] BIT
  - [IsRecurring] BIT
  - [AppointmentID] INT

If [IsRecurring] = 1, this would represent a block that recurs at the same time every week, and you should assume that start / end times are offsets calculated from the start of the week.

If [IsRecurring] = 0, this would just be a one-time occurrence and start / end times are absolute.

So each block in a doctor's schedule is accounted for, even if it doesn't fall precisely on each hour. Some strict business logic needs to be layered on top of this to ensure that no overlaps are created (unless for some reason you want to allow this).

To fit your example to this model you'd have something like this:

DoctorID | Start    | End      | IsAvailable | IsRecurring | AppointmentId | 
----------------------------------------------------------------------------
5477J    | 10AM Mon | 11AM Mon |           1 |           1 |          NULL |
5477J    | 11AM Mon | 12PM Mon |           0 |           1 |          NULL |
5477J    | 12PM Mon |  1PM Mon |           0 |           1 |     AppointID |
5477J    |  1PM Mon |  3PM Mon |           1 |           1 |          NULL |
5477J    |  3PM Mon |  4PM Mon |           0 |           1 |     AppointID |
5477J    |  4PM Mon |  5PM Mon |           0 |           1 |          NULL |
5477J    | 10AM Tue | 11AM Tue |           0 |           1 |          NULL |
5477J    | 11AM Tue | 12PM Tue |           1 |           1 |          NULL |
5477J    | 12PM Tue |  1PM Tue |           0 |           1 |     AppointID |
5477J    |  1PM Tue |  3PM Tue |           1 |           1 |          NULL |
5477J    |  3PM Tue |  4PM Tue |           0 |           1 |     AppointID |
5477J    |  4PM Tue |  5PM Tue |           0 |           1 |          NULL |

Upvotes: 4

Related Questions