Doctor scheduling database design

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------------DoctorIDDayOfWeekStartTimeBreakStartTimeBreakEndTimeEndTimeDoctorAppointment----------------DoctorIDDateAppointmentStartTimeAppointmentEndTime

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.

1. doctors: (This table will have details about the doctor)patients: (This table will have details about the patient)
2. specialization: (It defines the specialization of a doctor e.g. Dentist, Dermatologist etc.)
3. appointment_schedule: (Doctor will create an appointment schedule)
4. booking_status (Has different status for booking an appointment)
5. bookings (when a user books an appointment for a particular schedule and for a particular date and time)

-- Table structure for table 'appointment_schedule'CREATE TABLE IF NOT EXISTS 'appointment_schedule' (  'id' int(11) NOT NULL AUTO_INCREMENT,  'doctors_id' int(11) NOT NULL,  'working_days' varchar(50) NOT NULL,  'morning_time_start' time DEFAULT NULL,  'morning_time_end' time DEFAULT NULL,  'morning_tokens' int(11) NOT NULL,  'afternoon_time_start' time DEFAULT NULL,  'afternoon_time_end' time DEFAULT NULL,  'afternoon_tokens' int(11) NOT NULL,  'evening_time_start' time DEFAULT NULL,  'evening_time_end' time DEFAULT NULL,  'evening_tokens' int(11) NOT NULL,  PRIMARY KEY ('id')) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;-- ---------------------------------------------------------- Table structure for table 'bookings'CREATE TABLE IF NOT EXISTS 'bookings' (  'id' int(11) NOT NULL AUTO_INCREMENT,  'doctors_id' int(11) NOT NULL,  'appointment_schedule_id' int(11) NOT NULL,  'patients_id' int(11) NOT NULL,  'diseases_description' text NOT NULL,  'datetime_start' datetime NOT NULL,  'datetime_end' datetime NOT NULL,  'status_id' int(11) NOT NULL DEFAULT '1',  PRIMARY KEY ('id')) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;-- ---------------------------------------------------------- Table structure for table 'booking_status'CREATE TABLE IF NOT EXISTS 'booking_status' (  'id' int(11) NOT NULL AUTO_INCREMENT,  'name' varchar(25) NOT NULL,  PRIMARY KEY ('id')) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;-- Dumping data for table 'booking_status'    INSERT INTO 'booking_status' ('id', 'name') VALUES(1, 'Pending for Approval'),(2, 'Approved & Booked'),(3, 'Cancelled by User'),(4, 'Visited'),(5, 'User failed to Visit');-- ---------------------------------------------------------- Table structure for table 'doctors'CREATE TABLE IF NOT EXISTS 'doctors' (  'id' int(11) NOT NULL AUTO_INCREMENT,  'name' varchar(50) NOT NULL,  'specialization_id' int(11) NOT NULL,  'clinic_name' varchar(50) NOT NULL,  'address' varchar(1000) NOT NULL,  'qualification' varchar(50) NOT NULL,  'rating' int(11) NOT NULL,  PRIMARY KEY ('id')) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;-- ---------------------------------------------------------- Table structure for table 'patients'CREATE TABLE IF NOT EXISTS 'patients' (  'id' int(11) NOT NULL AUTO_INCREMENT,  'first_name' varchar(50) NOT NULL,  'last_name' varchar(50) NOT NULL,  'address' varchar(500) NOT NULL,  'contact' varchar(100) NOT NULL,  PRIMARY KEY ('id')) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;-- ---------------------------------------------------------- Table structure for table 'specialization'CREATE TABLE IF NOT EXISTS 'specialization' (  'id' int(11) NOT NULL AUTO_INCREMENT,  'name' varchar(100) NOT NULL,  PRIMARY KEY ('id')) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;