I am building a reservation system and I wanted to get some input from the community.

The system will have the following:

rates table = allows date ranges with various rates for each property
reservations table = includes reservation_start and reservation_end date ranges
rates_x_reservations table = table to handle the various combinations of the many to many relationship.

What I am wondering is how I will handle a situation where I get a reservation that has a date range that runs into multiple rate dates ranges.

For example, reservation is made for 2-1-10 through 2-13-10 but the rates changes on 2-9-10. Since I have to figure out how much the charge will be, I'll need to get the two totals from each part of this range and create the invoice.

Does anyone have a suggestion on how to handle this? I appreciate any help.

Tables:

CREATE TABLE `reservations` (
`reservation_id` bigint(20) unsigned NOT NULL auto_increment,
`reservation_created` timestamp NOT NULL default CURRENT_TIMESTAMP,
`reservation_approved` datetime default NULL,
`reservation_accepted` datetime default NULL,
`reservation_start` datetime NOT NULL,
`reservation_end` datetime NOT NULL,
`reservation_total` decimal(7,2) NOT NULL,
`reservation_fee` decimal(5,2) NOT NULL,
`reservation_expire` datetime NOT NULL,
`reservation_status` enum('Initiated','Approved','Accepted','Pending',' Completed') NOT NULL default 'Initiated',
`reservation_owner_deadline` datetime default NULL,
`user_id` bigint(20) unsigned NOT NULL,
`property_id` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`reservation_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `rates` (
`rate_id` bigint(20) unsigned NOT NULL auto_increment,
`rate_name` varchar(31) NOT NULL,
`rate_from` date NOT NULL,
`rate_to` date NOT NULL,
`rate_price` decimal(9,2) NOT NULL,
`rate_active` tinyint(1) NOT NULL default '1',
`user_id` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`rate_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `reservation_x_rate` (
`reservation_id` bigint(20) unsigned NOT NULL,
`rate_id` bigint(20) unsigned NOT NULL,
KEY `reservation_id` (`reservation_id`),
KEY `rate_id` (`rate_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

yeah, that sounds like what you'll have to do

it's a two step process

why can't you do the calculation with your application language?

I can, I guess. It's just that I usually try to do as much as I can in the database.