Forum Moderators: coopster
Any suggestions on a reasonably efficient way to model an availability list using PHP/MySQL.
Imagine a simple car rental agency with one car type, say Honda Civic.
Initially on Jan. 1, there are 10 civics available, and there is one record in the DB:
(FROM_DATE, TO_DATE, num_units).
So, the initial record would be: (Jan. 1, Dec. 31, 10)
Then someone books one from Jan. 3-8. Now there are three records in the database:
(Jan. 1, Jan. 2, 10)
(Jan. 3, Jan. 8, 9) ==> one less civic because one was rented.
(Jan. 9, Dec. 31, 10)
You get the message.
After a while, this splitting creates many records. Answering availability questions may need to span several records. Reserving a car would almost always involve splitting a record into multiple records. Returning a car would involve merging several records together.
Basically, the above approach wants to implement a linked list in a database. This sounds like a nightmare performance wise.
I thought perhaps of using a BLOB record, and storing the entire linked list serialized in the BLOB, reading the whole BLOB into PHP, then managing the list in PHP.
There has got to be a smarter way of doing this. Any advice?
The cars table has all the detailed information about each car, with an unique id for each car.
Then the rentals table has a a date when the rental is given out, and a date that the rental is brought back, along with who rented what car(unique id) and any other details you want to include.
Then to check for availability, you would only need to query the rentals table and select rows for the given car id number to see if the current date is within the range of the rental dates. If a row is returned then you know that the car is currently not available.
I hope this works out better for you. It at least makes sense to me ;)
Good luck!
It doesn't allow for a question from a customer like: Can I please rent two cars from the 4/Jan to 7/Jan, or when the rental car company has 10 cars to rent?
Current thinking is to create a table:
AVAILABILITY (date, car_type, num_available)
Have one row in the table for each day, prepopulate 365 days into the future.
1. Checking Availability
------------------------
Availability checks would be fast:
SELECT MIN(num_available) AS min_cars
FROM Availability_table
WHERE (date >= from_date AND date <= to_date)
min_cars above would give the lowest # cars avail at any point in your desired range. If you wanted to rent two cars for the entire range, min_cars would have to be 2 or more.
2. Renting out/ Returning a Car
---------------------------------
UPDATE Availability_table
SET num_available = num_available - 1 (+ 1 if returning)
WHERE date >= today AND date <= last date of reservation
This would involve updating several records, but is all within single SQL query, no process switching back to PHP.
Any better solutions?
