DB Schema for Appointment Booking App: What is the correct relationship between Doctors, Appointments, TimeSlots, Patients?

Question

[ UPDATED SCHEMA: https://i.stack.imgur.com/kBtno.png ]

I'm tasked with developing an appointment booking system that is designed for a small medical office. It is a Rails 3.2 app.

I'm having difficulty designing a database schema that makes sense to me.

Question: Given the following information, what is the correct relationship between doctors, patients, appointments, chairs and time_slots?

Patients need to make appointments with a doctor's office. Depending on the type of appointment, each appointment is scheduled for one or more adjacent time_slots, and whether or not there can be two appointments scheduled for time_slots with the same start_time and end_time is determined by appointment type. (Double-booking is allowed based on the type of appointments.)

App Specs:

1. Registered users make appointments via an appointment request form on the website.
2. Appointments take up a certain pre-set amount of adjacent time_slots. This is determined by appointment category/type. This length can be adjusted by the admin, as well as the length of each time_slot.
3. To help speed up the request process, unavailable/already-booked times are hidden from the calendar on the appointment request form.
4. On the admin-facing interface, administrators can confirm an appointment-request and make an appointment, and they can also update, create and delete scheduled appointments.
5. All appointments are held in a "chair"--like a dentist's chair. An office has multiple chairs. One patient per chair for a given booked time slot.
6. Appointments have fields for date, time of day, length appointment_type, double_bookable? (determined by appointment_type). Time_slots have a start_time and end_time, and a date.
7. There is only one doctor in this office. But, certain types of appointments--that are less demanding of the doc's time-can be double-booked. In essence, two teeth cleanings can be booked for the same time slot, as long as they are held in **separate chairs**.

My Relationships:

Office < ActiveRecord::Base
has_many :doctors

Doctor < ActiveRecord::Base
has_many :patients
belongs_to :offices

Patient < ActiveRecord::Base
belongs_to :doctor
has_many :appointments

Appointment < ActiveRecord::Base
belongs_to :patient
has_many :filled_time_slots

FilledTimeSlot < ActiveRecord::Base
belongs_to :appointment
belongs_to :time_slot

TimeSlot < ActiveRecord::Base
has_many :filled_time_slots
belongs_to :chair

Chair < ActiveRecord::Base
has_many :time_slots

Answer 1

I would do it like this:

Doctor
  has_many :appointments

Patient
  has_many :appointments

Office 
  has_many :chairs  

Chair
  #key-fields: office_id
  belongs_to :office
  has_many :appointments  

TimeSlot
  #key-fields: starts_at, :ends_at  

Appointment
  #key-fields: doctor_id, chair_id, time_slot_id, patient_id
  belongs_to :doctor
  belongs_to :chair
  belongs_to :time_slot  
  belongs_to :patient

The booking form is going to consist of getting all available time slots, and then, for each time slot, showing chairs which don't have an appointment in that time slot.

Answer 2

I would add a boolean field to TimeSlot and write a custom validation allowing for conditional double-booking on TimeSlot. Associations below. (With the boolean field in your migration for TimeSlots, you could get rid of the FilledTimeSlots table.)

Office < ActiveRecord::Base
  has_many :doctors

Doctor < ActiveRecord::Base
  has_many :patients, through: :appointments
  has_many :appointments
  belongs_to :office

Patient < ActiveRecord::Base
  has_many :doctors, through: :appointments 
  has_many :appointments

Appointment < ActiveRecord::Base
  belongs_to :patient
  belongs_to :doctor
  belongs_to :chair
  belongs_to :timeslot

TimeSlot < ActiveRecord::Base
  validates_with :availability, unless: "appointments.nil?"
  validates_with :workday
  has_many :appointments
  has_many :chairs, through: :appointments 
#you could then specify a maximum of 2 chairs in your validation depending on the appointment type, something like this:

def availability
  if self.chairs.count == 0
     self.booked? == false
  elsif self.chairs.count == 2
     self.booked? == true
  elsif self.chairs.count == 1 and self.appointment.type == "cleaning"
     self.booked? == false
  else
      self.booked? == true
  end
end


Chair < ActiveRecord::Base
  has_many :appointments
  belongs_to :timeslot, through: :appointment
end

------------ Alternate Answer by Max Williams ------------------

 Doctor
   has_many :appointments

 Patient
   has_many :appointments

 Office 
   has_many :chairs  

 Chair
   #key-fields: office_id
   belongs_to :office
   has_many :appointments  

 TimeSlot
  #key-fields: starts_at, :ends_at  

Appointment
  #key-fields: doctor_id, chair_id, time_slot_id, patient_id
  belongs_to :doctor
  belongs_to :chair
  belongs_to :time_slot  
  belongs_to :patient