PHP/MySQL Reservation System

Question

I'm currently working on an Equipment Reservation System for my school.

Here's basically what my tables look like:

tblEquipment:

     id    name          description
      1   Camera        Takes pictures
      2   Projector     Projects images
      3   Stereo        Plays music

tblEvents:

      id       equipmentID        start             end
       1          2,3           1251312300     1251315900    //Should I use comma delimited entries for equipmentID?   
       2           1            1251312300     1251315900    

Regarding my project, I have a couple of questions:

1) If multiple pieces of equipment are being reserved, (which will happen more times than not) should the "equipmentIDs" be comma delimited in the equipmentID field?

2) Currently, when a user makes a reservation, they first select their "requested times", then are presented with available items at that time. Here's what I am using for that query:

$start = //user's requested time
$start = //user's requested time

SELECT equipmentID FROM tblEvents
 WHERE ($start >= start && $start <= end) 
 OR ($end >= start && $end <= end)
 OR ($start <= start && $end >= end

 while($row = mysql_fetch_array($data)) {

  echo $row['equipmentID'];  //Would echo something like: 
  echo "<br>";               //    2,3
                            //    1

 }

My question is this:

How can I take the 'results' of the above query to then re-query the 'tblequipment' table, but exclude the items that were in the 'results' above (because they would not be available). Keeping in mind, that my query above may return multiple rows.

Any help on this would be great, thanks!

Answer 1

1. No, don't use comma-seperated values. If you want a user to have the ability to check-out multiple items, you'll need a new table:

   Tables: Users, Property, Checkout

   The new Checkout table will have the following fields:

   • id
   • person_id
   • property_id
   • checkout_date
   • checkin_date
     
     

   This table can have multiple entries for any particular user. A user may be in there once for a company laptop, and again for a company projector:

   1 | 12 | 23 | 2009-08-17 | 0000-00-00
   2 | 12 | 28 | 2009-08-17 | 0000-00-00

2. As for checking if an item is reserved, I would as a field in the property table to hold a boolean value:

   is_reserved (BOOL)

   Finding items that are available is nothing more than checking all items with a BOOL value of false, and no presence in the checkout table coupled with no checkin_date.

Answer 2

Regarding #1: No! No, no, no, no, no! If you have multiple pieces of equipment being reserved, then you should have multiple rows in the reservations table (what looks like tblEvents here). To avoid duplicating the other fields in tblEvents, you'd typically create a third table, perhaps tblEventEquipment that simply lists what equipment belongs with what event.

If you need a comma-separated list for the purposes of output (which doesn't seem likely), you can always generate one with GROUP_CONCAT(), but inside the table you want one row per reserved piece of equipment. Otherwise, SQL cannot effectively (or efficiently) determine what equipment is reserved at a particular time.

Regarding #2, you want a query like:

SELECT * 
FROM tblEquipment
WHERE NOT EXISTS (
  SELECT 1 
  FROM tblEvents 
  WHERE tblEvents.equipmentID = tblEquipment.equipmentID
    AND $end >= start AND $start <= end
)

This selects equipment for which there isn't a reservation. Note that I simplified your logic for determining whether the equipment is reserved or not by doing just two comparisons.

Finally, an unrelated note: I recommend strongly against storing timestamps as integers in the database table. Use MySQL's built-in DATETIME type. If you need to convert to/from a timestamp, you can use the UNIX_TIMESTAMP() and FROM_UNIXTIME() functions.