title: Rooms & Availability Dates PHP/MySQL
body: <p>So, I'm desinging a web application that is going to work for a Hotel and it should eventually become an easy way to install for every hotel website with some extra code. The application is <strong>working with php and mysql</strong>. So, as you probably guessed, I'm pretty confused with the database structure. Though, I've made some research and I'm not just asking for a full-fledged system for bookings. </p>
  
  <p>So, owner of the hotel has to have many types of rooms. So probably a table for rooms is a "must" I suppose. Here we go:</p>
  
  <pre><code>---------- Rooms Table (example)
  
  ID     NAME         ADULTS     ROOM_COUNT     PRICE/NIGHT     
  
  1      DoubleRoom   2          5              45$
  2      Family       3          2              75$
  3      Single       1          3              35$
  </code></pre>
  
  <p>This is pretty straightforward. <strong>Rooms can be added through my application</strong> and I'll also attach a proper description as well as a variety of images for each room. But let's get back to the point. People should make bookings so...</p>
  
  <pre><code>---------- Bookings Table (example)
  
  ID    ROOM_ID      BOOKING_USER     CHECKIN (timestamp)   CHECKOUT (timestamp)
  
  1     2            A full name      208420842042          308230482042824
  2     3            A full name      208423242525          529752972595272
  ...
  </code></pre>
  
  <p>So, this is -<strong>hopefully</strong>- the database structure. Now, I'm thinking of a way to <strong>check for availability</strong>. I know, it might sound stupid but I can't figure out a smart and easy way. In the form of the website, there should be the following fields: <code>check-in date</code> and <code>check-out-date</code> which will be then transformed to timestamps and.. well here's the time I stuck!</p>
  
  <p>How can I check for availabily of rooms when I only have the above tables. Is there any way or I need to change my database structure? Even if I manage to check for availability, <strong>how comes the total number of rooms</strong> (<code>ROOM_COUNT</code>) <strong>in count</strong>? I mean, how can I check availability for multiple types of rooms? I thought something like:</p>
  
  <pre><code>SELECT count(ROOM_ID) FROM bookings WHERE
  bookings.checkin &lt; DATE_GIVEN_AS_CHECKIN_INPUT and
  bookings.checkout &gt; DATE_GIVEN_AS_CHECKOUT_INPUT
  </code></pre>
  
  <p>then depending on the output of this query I would know if there's a room available or not. But what about room count? Is there any better ways?</p>
  
  <p>I'd be really glad if someone here could enlight me on this. Best regards and thanks in advance, even for reading this!<br>
  Blane</p>
  
  <p>P.S. Oh, it's not a homework! :P I don't go to school anyway :D</p>