Table design for hotel reservation system

[needs_upgrade]

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

[imdead]

yeah that would work.

[tibberous]

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.

 

[akitchin]

in my opinion the way MySQL handles dates is fantastic, provided you use the date functions given.  tibberous, what problems have you run into previously with MySQL's dates?

[imdead]

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

[corbin]

"return date("Y-m-d H:i:s", $timestamp);"

 

You realize that will not be GMT, right?

 

The hour offsets are done in functions like date.  Timestamps themselves are always GMT.

[needs_upgrade]

thanks for the reply guys.

[corbin]

Oh, to add something useful to this post, you'll probably want to create an index on room_id since it will be used as search criteria sometimes.

[cooldude832]

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.

[Barand]

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

[needs_upgrade]

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.