needs_upgrade Posted August 14, 2008 Share Posted August 14, 2008 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 Quote Link to comment Share on other sites More sharing options...
imdead Posted August 14, 2008 Share Posted August 14, 2008 yeah that would work. Quote Link to comment Share on other sites More sharing options...
tibberous Posted August 15, 2008 Share Posted August 15, 2008 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. Quote Link to comment Share on other sites More sharing options...
akitchin Posted August 15, 2008 Share Posted August 15, 2008 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? Quote Link to comment Share on other sites More sharing options...
imdead Posted August 15, 2008 Share Posted August 15, 2008 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 Quote Link to comment Share on other sites More sharing options...
corbin Posted August 15, 2008 Share Posted August 15, 2008 "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. Quote Link to comment Share on other sites More sharing options...
needs_upgrade Posted August 16, 2008 Author Share Posted August 16, 2008 thanks for the reply guys. Quote Link to comment Share on other sites More sharing options...
corbin Posted August 16, 2008 Share Posted August 16, 2008 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. Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted August 16, 2008 Share Posted August 16, 2008 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 17, 2008 Share Posted August 17, 2008 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 Quote Link to comment Share on other sites More sharing options...
needs_upgrade Posted August 20, 2008 Author Share Posted August 20, 2008 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.