Database design: dealing with availability

Question

In my Ruby on Rails app, bike rental companies can manage all their bikes (reservations, payments etc.).

Context I would like to offer a bike rental companies (shops) the option to implement a booking form on their own website, so they can let customers make a reservation for a bike.

• This booking form would then show bike_categories of which bikes are available for a given arrival and departure date.

Question After reading some documentation online, I think I should create a separate table to deal with the availability per bike_category, which would then show the the count of bikes available for the selected arrival and departure date.

=> example: between 1-1-2020 and 1-2-2010 20 bikes of bike_category "mountainbike" are available

But I'm unsure how the structure of the table should be, as a reservation is on a bike-level, which consequently belongs to a bike_category?

Code

models

class Shop < ApplicationRecord
  has_many :bike_categories, dependent: :destroy
  has_many :bikes, through: :bike_categories
  has_many :reservations, dependent: :destroy
end

class Reservation < ApplicationRecord
  belongs_to :shop
  belongs_to :bike
end

class Bike < ApplicationRecord
  belongs_to :bike_category
  has_many :reservations, dependent: :destroy
end

class BikeCategory < ApplicationRecord
  belongs_to :shop
  has_many :bikes, dependent: :destroy
end

Answer 1

I personally wouldn't create another table for this. I would query the user for the dates they intend to rent the bikes. Once you have the start and end dates, i'd run a query on the reservation table to find which bikes are unavailable during this time.

E.g.

reserved_bikes = Reservation.distinct.pluck(:bike_id).where(start_time: Time.now..user_end_time, end_time: Time.now..user_start_time)

Then I'd use these to find out how many of each category there are

Bike.where.not(id: reserved_bikes).group(:category).count

There's probably a way to do the above with joins in one query too if you look into it.

Generally I avoid adding more tables when the data is already able to be inferred. The main reason being that if you update the original table (in this case reservations), you'll then have to cascade those updates to the "inferred data" tables which is a mess to maintain.