Booking Out System

Hi all, I’m working on a lens-hire website. I want the admin user to be able to book out a lens for a period that can vary. On the product page, I want to say when the lens is next available for hire. I guess all I will need is one table of when the lens is unavailable but not sure how to implement this…any ideas is appreciated :slight_smile:

Interesting.

You only really want to store a booking.from and booking.to in your database, you don’t really need more than this.

I guess the trick would lie on discovering the gaps that lie in between the two dates.

I suspect a certain Underworld loving, Sitepoint member would know how to this straight away. :wink:

Let’s see what everyone says, it’s an interesting problem.

Are the booking periods in units of days? I think I may have it…

Hi bud, yeah pretty much it will be in units of days, someone might want three day hire or maybe seven days…it’s a tricky one, your right, it’s how to find the time between these dates, I’m snookered :stuck_out_tongue:

In which case, just ask the database for all the days that lens is booked for, then just display the ones that aren’t present in the result set.

With some nifty SQL, you could probably just ask the database for these too.

Ok, yeah that’s making sense, just struggling to work out how I could do this in my head lol! Any ideas on how I could code this up? :smiley:

Sounds like all that is needed is two tables one for the lenses and another for the bookings.

lens

  • id
  • … (perhaps there is a synthetic key… I have no idea)

lens_booking

  • id
  • lense_id
  • start (datetime)
  • end (datetime)
  • … (whatever else)

That is probably the simplest way to achieve it leaving it up to the application to keep the integrity of the data in tact ie. making sure dates don’t overlap, end is after start, etc.

Hey oddz,

Maybe you missed coxdabd request:-

On the product page, I want to say when the lens is next available for hire.

Hey dude, yeah this is what I’m in need of, I guess a bit of SQL is what I need. So I basically need to check the database to see if the lens is out, if it is, then display when the lens is next available…I’ve got an idea…I’ll have a play… :slight_smile:

So you have tables already in place? If so post the create statements. Otherwise, the tables need to be in place before you can pull any data from them, obviously. I thought you were asking about table structure and all.

Hi, no I don’t have anything in place at the moment. The problem is the following: I’ll have two dates in my table IF the lens is on hire on or between these dates then I need to display to the customer when the lens will be back instock and available for hire. As Anthony has mentioned, I think some nifty SQL could sort this?

not until you create the tables, lol

why not go with the design that oddz suggested, and load up some sample data, then dump the table and let us have it for testing purposes

Hey bud, sure thing, I’ll get onto this this afternoon, I’ve just got to head down for a long and boring wait in A&E - not for me though! Thanks for the help dude :slight_smile:

Hey dude, ok I have a few screen grabs of my database layout and some data within them. Hope this helps. Let me know if you’d like me to get anything else :slight_smile:

Products Table

Bookings Table

Why are you storing the time too? Is this relevant, I didn’t think it was?

Hi dude, yeah no I guess we don’t need time, just days. I followed oddz table structure which was datetime. So yeah, lets just stick with date bud :slight_smile:

they way i would approach the problem is as follows

some lenses are already hired out – these are identified by the fact that today is between the start and end dates

so the “next available for hire” date is the end date + 1 day (not sure how you want to handle this, whether it is available on the same day it’s returned, or only on the day after)

if today is before the start date of a future booking, or after the end date of a past booking, or if there never was a booking for it, then that lens is theoretically available today

so this requires two SELECTs, which can be combined in a UNION –

SELECT products.products_id
     , products.products_name
     , products.products_url
     , bookings.end + INTERVAL 1 DAY AS next_available
  FROM products
INNER
  JOIN bookings
    ON bookings.products_id = products.products_id
   AND CURRENT_DATE BETWEEN bookings.start AND bookings.end
UNION ALL
SELECT products.products_id
     , products.products_name
     , products.products_url
     , CURRENT_DATE AS next_available
  FROM products
INNER
  JOIN bookings
    ON bookings.products_id = products.products_id
   AND CURRENT_DATE BETWEEN bookings.start AND bookings.end
 WHERE bookings.products_id IS NULL

Hey there, thank you for your detailed reply, think that will be a massive help. I’ll have a play and see how I get along. I’ll keep you posted in my progress. Thanks for the help and advice, it’s appreciated :slight_smile:

Is this getting too complicated?

This could be simplified if advance bookings are not required.

When taking a booking, store the start and end plus other details, in a bookings table and then write the end date into the lens table.

In the lens table, you could create a field “availableFrom” and then when processing the booking work out the availability (ie end date + 1)

When displaying availability if the date is in the past then the lens is available (you may need a separate mechanism for confirming it actually came back). If the date is in the future then display that date.

If the availableFrom field is a property of the lens then you can change it for other reasons (such as if the lens is damaged) without having to pretend that you have a booking.

The bookings table still has a place so that you can call up the history of a lens, and do things like seeing which lens gets most bookings.