Listing available users on a certain date

Question

I'm looking at making a booking system and I need to display all the available users on a particular day/time.

Thinking about it, I'm going to set up a table to keep note of when people AREN'T available, and assume that they are otherwise available. The structure I had in mind was to keep a note of the date_start of when they won't be available, and then the date_end.

Now the problem arises when I'm trying to book a meeting, if I want to book something on Tuesday for 11:00 until 13:00 I need to fetch a list of those people that are available then, i.e. don't have any holidays booked at during that time.

Any ideas or thoughts? I'll be using Ruby on Rails if that helps/hinders anyone's ideas.

Answer 1

Here's one way to model it. Lets say we have a model 'Engagement' which has a start datetime, end datetime and name. An engagement has many users, through another join table called 'user_engagements' (with corresponding UserEngagement model). So we have

User
  has_many :user_engagements
  has_many :engagements, :through => :user_engagements

Engagement
  #fields - starts_at, ends_at (both datetime)
  has_many :user_engagements
  has_many :users, :through => :user_engagements

UserEngagement
  belongs_to :user
  belongs_to :engagement

Now we have a nice simple schema. An engagement basically models something that's happening, and user_engagements model users who are booked to do that thing. We have an assumption (not written into the code) that when they are doing something they aren't available to do anything else.

Our next task is to write a method that returns users available within a given time period, ie a new engagement. So, we make an engagement and we want all the users who don't have an engagement which crosses over with our new engagement. I think the simplest way of doing this might be to find all the users who do have a crossing-over engagement and then return all the users who aren't them. If you know what i mean. A more precise way of saying e2 crosses over with e1 is that e2 starts before the end of e1 AND ends after the start of e1.

Let's make this a method of an engagement object since it's totally dependent on an engagement's data.

#in Engagement
def unavailable_user_ids
  User.find(:all, :include => [:user_engagements], :select => "users.id", :conditions => ["user_engagements.starts_at < ? and user_engagements.ends_at > ?", self.ends_at, self.starts_at]).collect(&:id)
end

def available_users
  User.find(:all, :conditions => ["id not in (?)", self.unavailable_user_ids])
end

I feel like there's a more efficient way to get this in one query but i can't quite put my finger on the sql.