I have been writing software for a while but until now have managed to
avoid almost any connection with sql and now I think I have a sql
problem. I have written and released a FOSS Campground Reservation
System (http://open-campground.rubyforge.com) and I am having a problem
with a new feature I want to incorporate.
In the system I have a reservations table which refers to a spaces table
which refers to a price table which contains the rates (daily, weekly
etc.) which vary depending on the features of the spaces. When I want a
rate associated with a space I can simply do @space.price.daily_rate and
there we have it. Simple. Now I have a request to change the system so
that I can have differing rates for different times like winter rates
and summer rates and holiday rates etc.
The tables currently look like this (many details omitted):
create_table “reservations”, :force => true do |t|
t.column “space_id”, :integer, :default =>
0, :null => false
# … (a lot of unrelated details of the reservation)
end
create_table “spaces”, :force => true do |t|
t.column “name”, :string, :limit => 24, :default =>
“”, :null => false
# each space may refer to a different price or many may refer to a
single price
t.column “price_id”, :integer, :limit => 3, :default =>
0, :null => false
# … (a lot of unrelated details of the space like size and
equipment)
end
create_table “prices”, :force => true do |t|
# name is for administrative convenience only
t.column “name”, :string, :limit => 24, :default => “”,
:null => false
t.column “daily_rate”, :integer, :limit => 5, :default => 0,
:null => false
t.column “weekly_rate”, :integer, :limit => 5, :default => 0,
:null => false
t.column “monthly_rate”, :integer, :limit => 6, :default => 0,
:null => false
end
I have come up with a season table which includes the start and end
dates and a name along with a method that returns the season for a
reservation given the dates. I am trying to understand how I can
shoehorn this concept into my system. I want to be able to, in a simple
manner, retrieve the rate applicable to a given space during a given
season. I know I will have to modify the prices in some manner to
identify the association of price to season but how do I tie it all
together?
Thanks for any help
Norm