Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009
    Posts
    7

    Unanswered: Multiple Date Range Rates

    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;
    Last edited by wowdezign; 12-30-09 at 00:57. Reason: Posted Tables

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by wowdezign View Post
    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.
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2009
    Posts
    7
    I can, I guess. It's just that I usually try to do as much as I can in the database.
    Last edited by wowdezign; 12-30-09 at 09:36.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •