Jump to content

Table design for hotel reservation system


needs_upgrade

Recommended Posts

Hi guys!

 

Assuming that i already have the room table, how should i design the table for room resevations?

this is what i have in mind....

 

CREATE TABLE room_reservations (

    room_reservation_id INT(6) NOT NULL AUTO_INCREMENT,

    room_id INT(4) NOT NULL,

    check_in DATE NOT NULL,

    check_out DATE NOT NULL,

    PRIMARY KEY(room_reservation_id)

);

 

Would this work? For example, if room#1 has a reservation from january 15 - 20, that room#1 will not be showed if i search for any rooms available for january 17. or if i have selected room#1, it will show the days it is  available

 

thnx guys

Link to comment
Share on other sites

Yep.

 

Personally I don't like how mysql handles dates, so I store them integers. You can take a timestamp, divide it by 86400 (seconds in a day) and floor it, and that will give you days sine the epoc. You can store that, and make it back into a timestamp by multiplying it by 86400.

 

It sounds worse than it is, and give you the advantage of being able to work with numbers until you actually go to print the value. Only real disadvantage if that you can't use mysql's date functions on it.

 

Link to comment
Share on other sites

i just use

// Returns the current time in GMT in MySQL compatible format.
function get_date_time($timestamp = 0)
{
  if ($timestamp)
    return date("Y-m-d H:i:s", $timestamp);
  else
    return gmdate("Y-m-d H:i:s");
}

 

Then when inserting dates just call get_date_time()

 

so i use datetime in phpmyadmin for the table

Link to comment
Share on other sites

Slight mod reasons below

CREATE TABLE room_reservations (
     room_reservation_id INT(6) NOT NULL AUTO_INCREMENT,
     room_id INT(4) NOT NULL,
     check_in_want DATETIME NOT NULL,
     check_in_actual DATETIME NOT NULL,
     check_out_want DATE NOT NULL,
    check_out_actual DATE NOT NULL,
     PRIMARY KEY(room_reservation_id)
);

reason being is when you make a reservation you do not always check in or out when you were suppose to so this keeps track of things from you stand point without tainting the original reservation data.

Link to comment
Share on other sites

I fail to see any advantage in holding dates as integer unix timestamps rather than as DATE or DATETIME.

 

Almost all the internal datetime functions in MySQL expect DATE/TIME fields, so to use them you have to convert with FROM_UNIXTIME first. Not only that, if you want to examine the table data using something like QueryBrowser they are totally illegible unless you instinctively recognise that 1219012994 is 23:43 on 17 Aug 2008

Link to comment
Share on other sites

thanks cooldude for your modifications. but still, it depends upon the business rule  of the hotel. some hotels require payment for reservations and if you dont check in on time, service is considered sold.

 

i find mysql date functions very useful, actually. they are very helpful in report generation.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.