Adding a table modifying the data I fetch

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
() 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