How to create a database that shows a calendar of availability for a set of apartments?

database, database-design, postgresql, ruby-on-rails, sql

Solution

You should not store availability but the opposite (apartment is booked for a specific date). Without any deeper analysis I would do something as simple as:

owner
  owner_id
  owner_name

apartment
  apartment_id
  apartment_name
  apartment_description
  owner_id

customer
  customer_id
  customer_name

booking
  booking_id
  customer_id
  apartment_id
  booking_start
  booking_end

In case when one can book disjoint days:

booking
  booking_id
  customer_id
  apartment_id

booking_calendar
  booking_id
  booking_date

In any case you will be able to return list of available apartments quite easy.

select
 *
from
 apartments a
 where not exists 
  (select 
     1
   from 
     bookings b 
   where 
      a.apartment_id = b.apartment_id 
      and (
        <<required_start>> between booking_start and booking_end
        or
        <<required_end>> between booking_start and booking_end
        )

Problem

Context: The best example is AirBnB. Let's say I have 5 apartments. Each apartment has a calendar that represents it's availability. When a vacationer travels to my city and searches for apartments using a given start date and end date, if that period of time shows up as available on the calendar for any of my apartments, I want those apartments to be shown in search results for the vacationer. One bit at a time: Obviously there's a lot in the above. The scope of this question is how I should set up database for the list of apartments that includes their availability. Before building a database, I spent some time manually coordinating in Excel just to get a clearer picture in my head of what everything should look like. In the Excel, what I found worked to be column headers for table are: - apartment_name - owner_id - apartment_description - calendar Calendar right now is what I'm having trouble with. Literally in my Excel, the columns are just dates going on to eternity. Whenever a vacationer submits a request, I find all the apartments for which each date cell is empty (e.g., available). Then I send the vacationer these apartments. When s/he makes a booking, I go back to the Excel and mark unavailable in each date cell for the specific apartment chosen. I want to get more opinions... is this the right way I should imagine my database in PostGreSQL? And if so... can I just make a migration that looks like below? ``` class CreateApartments < ActiveRecord::Migration def change create_table :apartments do |t| t.string :apt_name t.integer :apt_owner t.text :apt_description Date.today..Date.new(2034, 12, 31)).each do |date| t.date :date end t.timestamps end end end ```

Original source