Building an appointment booking system in Rails

I am looking to build an appointment booking app with the following characteristics: - Users can be service providers or buyers - Service providers set their availabilities (but can only set their availabilities at a maximum of 6 months ahead) - Buyers can then book appointments based on those availabilities - each appointment, based on the type of service, takes a different amount of time - Based on the appointment that a buyer selects, a different set of availabilities is shown depending on how long the service takes

What I've built is the following: - A TimeSlot model where I create a number of generic 30 minute time slots based on a start_time and end_time attribute. In order to make these time slots extend 6 months into the future, I have a background job running each day that creates all the new time slots necessary

class TimeSlot < ActiveRecord::Base
  has_many :user_time_slots
  # ... more methods below
end

- A UserTimeSlots model that basically represents a service provider's availability that they can set. So when they create a user_time_slot, they are essentially saying that they are available at that time.

class UserTimeSlot < ActiveRecord::Base 
    belongs_to :time_slot
    belongs_to :service_provider, :class_name => "User"
    belongs_to :appointment
end

- An Appointment model that has many user_time_slots. It has many because an appointment belongs to a service which takes a certain amount of time (a time_required attribute on services) and it might span a number consecutive user_time_slots.

class Appointment < ActiveRecord::Base
  has_many :user_time_slots
  belongs_to :buyer, :class_name => "User"
  belongs_to :service_provider, :class_name => "User"
  belongs_to :service
end

- A Service model that has many appointments and belongs to a service provider who creates that service.

class Service < ActiveRecord::Base
  has_many :appointments
  belongs_to :service_provider, :class_name => "User"
end

This domain model works; however, I am wondering if there is a better way to do this because of the following:

• It seems a little clunky to me to be creating TimeSlot records every day on my backend using a background job - the TimeSlots really have the sole purpose of having a start and end time and then being associated to.

  • Once the user (buyer) selects a service they want, I am not sure how I would efficiently find the x number of user_time_slots that are consecutive and, therefore, available for booking the appointment (for example, if I have 30 minute time slot intervals and a user selects an appointment that will take 3 hours, I would have to find 6 consecutive time slots). For example, if a user clicks on an instance of a service I would have to 1) get the time required for that service (easy enough to do) and 2) I'd have to find ALL of the user's user_time_slots and collect their associated time_slots, then compare each time slot's start and end times to one another to find consecutive ones. This just seems like way too much iteration to me and seems like this will bog down my application!

Does anyone have a better way or solution to do this (particularly around the topic of finding consecutive time slots)?

Look like a fun project. :)

I would personally not model the "existing time", i.e I would not have a background job create "empty" data.

I would try a model like this:

Where the User table?

I would not use a shared User model for the two different User types. My gut feeling is that they need to be different, if not now, most surly over time. Also it makes the model more clear, in my opinion. If there is a login or any auth, I would add a User table for that specific data and rather have Service Provider and Consumer have some relation to this.

Service Provider manages availability

The Service Provider would only need an empty calendar (or similar), listing only her already entered Service Availability, per Service.

Consumer books appointment

The Consumer would search/browse/navigate Service Availability, per Service

Depending on business logic, i.e. will a Consumer always schedule the whole defined time slot? Or could the Consumer book a preferred time slot. As long as the booked slot is within the given Service's available time slot?

Scheduling

So we only put Service Availability records in when the Service Provider manages her availability for a specific Service. Empty is simply considered not available.

We only put Appointment records in when a Consumer books an Service, based on Service Availability.

If its possible for the Consumer to book only a part of a Service Availability time slot, I would recommend to create two (or one) new Service Availability records for the time left over. Checking if time is available could be done by comparing Service Availability with Appointment, but this would not be optimal. Better would be to just query Service Availability for a specific Service and get a availability schedule, where no record would mean no availability.

There are a lots of ifs here, depending on your specifc needs and requested business logic. But I hope this helps with some inspiration and ideas.