Doctor scheduling database design

Question

I got this below user interface for scheduling functionality.

I need to setup the doctors available time for some days for each week of the month.

How should i create the table for handling this.

I thought i can create a table structure with the below columns

1. DoctorId
2. Date
3. AvalableFrom
4. AvalilableTo

But here the problem is I will be adding 2 rows for the same doctor if he visit two different timings in same day.

Is there any issue in my database schema or any better table design will help me to do this simple?

With this table i should be able to detect a doctor is available or not for the particular day and time

How best i can improve my table design?

Edit: The screen which i am working is, to help the Hospital Staff to know when the visiting doctor is available, so they can book the appointment for the patient for a time or inform other available time

Answer 1

I would go with dividing the day into segments of 15 or 30 minutes, and creating a record for every doctor for each slot that they are available. It takes care of uniqueness very well.

An appointment can be a single record and the time slots that it covers can reference the appointment record.

Because it records non-appointment availability time in just the same way as appointment time, queries against this method are generally very simple -- for example, to query for available time slots of a given length, or to calculate how much of a doctor's time was not used for appointments, or the average length of appointments, etc..

The table would be something like:

create table staff_time(
  staff_id  integer,
  time_slot date,
  allocation_id)

allocation_id references an appointment or training time or other allocation, or nothing if the slot is free.

The date data type includes a time component on Oracle.

Answer 2

EDIT: I misunderstood the question.

Your design is fine - there's nothing wrong with having multiple rows in a table reflecting multiple evens. The only refinement you might consider is have AvailableFrom and AvailableTo to be datetime values, rather than time, so you can remove the "date" column. This helps you deal with availability spanning midnight.

The rest of the answer does NOT relate to the question - it's based on a misunderstanding of the issue.

Firstly, you need to know when a doctor's working hours are; this might be simple (9 - 5 every day), or complex (9-5 Mondays, not available Tuesdays, 9-12:30 Wednesday - Friday). You may also need to record break times - lunch, for instance - on each day, so you don't schedule an appointment over lunch; I'm assuming different doctors will take their breaks at different times.

Next, instead of recording "availability", you probably want to record "appointments" for each day. A doctor is available when their schedule says they are working, and when they don't have a scheduled appointment.

So, your schema might be:

Doctors
--------
DoctorID
....

DoctorSchedule
------------
DoctorID
DayOfWeek
StartTime
BreakStartTime
BreakEndTime
EndTime

DoctorAppointment
----------------
DoctorID
Date
AppointmentStartTime
AppointmentEndTime