SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2006
    Location
    Dubrovnik, Croatia
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Bookin period table

    Hi to all


    I am creating hotel booking table. Let's say I have just one type of rooms for booking (nothing special). Now i need to define periods when this room is available and to what price.

    Code:
    CREATE TABLE `book` (
      `book_id` INTEGER(11) NOT NULL AUTO_INCREMENT,
      `book_from` DATE DEFAULT NULL,
      `book_to` DATE DEFAULT NULL,
      `price` FLOAT(9,3) DEFAULT NULL,
      PRIMARY KEY (`book_id`)
    )
    So in here i enter 4 periods

    Code:
               book_id book_from  book_to                   price 
           1,000000 15.2.2009  20.5.2009                   100 
           2,000000 21.5.2009  21.7.2009                   150 
           3,000000 22.7.2009  22.9.2009                   200 
           4,000000 23.9.2009  14.2.2010                    70
    Ok so if user wants to check a room from 22.05.2009 to 22.06.2009 i know that this goes into second row and price will be 150*days

    But what when i have dates like this

    15.07.2009 up till 01.08.2009. Here i have overlaping with two rows with different prices. How can I extract with query this. Is think this is good way of handling it in table or maybe not?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by djomla View Post
    Is think this is good way of handling it in table or maybe not?
    yes, it is a good way of storing the data

    the easiest way to calculate the cost of the booking is to retrieve all the rows of the booking table which the booking spans -- in your example, from 2009-07-15 till 2009-08-01, this would retrieve rows 2 and 3

    then do the calculation in your front end language

    p.s. two tips -- don't use FLOAT for price, and start learning to write dates in year-month-day sequence (because you have to do that for mysql)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2006
    Location
    Dubrovnik, Croatia
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What to use for price, decimal?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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