I am working on a project for which I need to calculate prices of holiday homes available in a selected rental period. I need some help with building a SQL query that combines the following tables and convert the data into an output containing the price for the requested period for each house. It should contain the stay costs, and the additional cost types together with the amount the renter should pay for every cost_type.
I have a table costprofiles which enables the house owner to have multiple prices throughout the year:
+----------------+----------+--------------+ | costprofile_id | house_id | profile_name | +----------------+----------+--------------+ | 1 | 312 | summer | +----------------+----------+--------------+ | 2 | 312 | winter | +----------------+----------+--------------+
I have a table called costprofile_items which is linked to a costprofile via the foreign key costprofile_id. This table contains all different amounts a renter should pay to the owner if the price of the selected period uses this cost_type. Each additional amount can be calculated in four different ways:
- per night
- per stay
- per person
- per person per night
The way each amount contributes to the total rent price is stored in the calculation_type column. This is what the costprofile_items table looks like:
+---------------------+----------------+--------+-------------+----------------------+ | costprofile_item_id | costprofile_id | amount | cost_type | calculation_type | +---------------------+----------------+--------+-------------+----------------------+ | 1 | 1 | 20 | usage_cost | per_night | +---------------------+----------------+--------+-------------+----------------------+ | 2 | 1 | 8.5 | cleaning | per_stay | +---------------------+----------------+--------+-------------+----------------------+ | 3 | 1 | 0.82 | tourist_tax | per_person_per_night | +---------------------+----------------+--------+-------------+----------------------+
I also have the table prices in which each row represents a price per night that can be used between the start_date and the end_date (the weekday of the start_date equals the weekday of arrival at the house and the weekday of end_date equals the weekday of departure). The row also contains a column nights that determines how long a sub period needs to be in order to use this price. This is what the table looks like:
+----------+----------+----------------+------------+------------+-----------+--------+ | price_id | house_id | costprofile_id | start_date | end_date | per_night | nights | +----------+----------+----------------+------------+------------+-----------+--------+ | 1 | 1 | 1 | 2014-08-04 | 2014-12-01 | 60 | 7 | +----------+----------+----------------+------------+------------+-----------+--------+ | 2 | 1 | 1 | 2014-08-08 | 2014-12-05 | 70 | 3 | +----------+----------+----------------+------------+------------+-----------+--------+ | 3 | 1 | 2 | 2014-12-01 | 2015-03-02 | 0 | 1 | +----------+----------+----------------+------------+------------+-----------+--------+
In the table you can see that for the given house you can book the period from 8 till 11 August and this will cost 3*70 = €210 for the stay. If you are with 4 persons the additional costs are 3*20 = €60 for electricity/gas usage, €8.5 for cleaning and 0.82*4*3 = €9.84 for tourist tax. So the total cost of your weekend will be €288.34. It also should be possible to combine this weekend with for example 2 times the weekly price as described in the first row of the table. In this case the price from 8 till 25 August would be 288.34 + 2*582.96 = €1454.26. Note that the calculation types per_stay and per_person only need to be selected from the first sub period, so the cleaning in the last example is only paid once.
The last table I use for calculating prices is the table prices_per_group. This table is connected to prices via the foreign key price_id. In the prices table above you can see in the last row that the price per night equals 0. In that case the owner had given a price per night for every number of persons that he accepts in his house during this period this price is active. This is the way those different prices are stored:
+--------------------+----------+------------+-----------+ | price_per_group_id | price_id | group_size | per_night | +--------------------+----------+------------+-----------+ | 1 | 3 | 5 | 50 | +--------------------+----------+------------+-----------+ | 2 | 3 | 4 | 45 | +--------------------+----------+------------+-----------+
As you can see a week starting at 1 December (or any Monday after that, but before 2 March) will cost €50 per night if you are with 5 persons or €45 if you are with 4.
I hope it is clear now how I am trying to store and compute all different prices.
I have managed to get these calculations working, by first querying all cost types of every available house with the following query:
SELECT * FROM (
SELECT prices.house_id,
prices.price_id,
prices.costprofile_id,
prices.nights,
prices.start_date,
prices.end_date,
MIN(
prices.per_night + COALESCE(prices_per_group.per_night, 0)
) AS per_night /* Add the price per night from prices and prices_per_group (if one has a non-zero value the other is always zero) */
FROM prices
LEFT JOIN prices_per_group ON prices.price_id = prices_per_group.price_id
WHERE prices.house_id IN (
/* Query that returns a set with the ids of all available houses here */
)
AND (
prices_per_group.price_id IS NULL OR /* If true, no row in prices_per_group is pointing to the price_id currently being evaluated */
prices_per_group.group_size >= 4 /* If true, the group_size satisfies the requested number of persons */
)
GROUP BY prices.price_id
) AS possible_prices
INNER JOIN costprofile_items ON costprofile_items.costprofile_id = possible_prices.costprofile_id
ORDER BY price_id ASC
After that I used PHP to loop through all rows containing price information for a certain house. I started at the start_date and made steps using the first usable price row it could find and repeated that until I am at the end_date. The problem with my current method is that it is too slow. For 1000 houses the webserver needs 0.3sec execution time. Maybe some optimization can be done in my PHP code, but I was hoping someone could help me with putting this all together in SQL. This way for example sorting by price is easier to implement and just asking for the large result after quickly executing the above query makes my execution time jump up to 0.12sec.
All help and advice is welcome
Advertisement
Answer
In the end I decided to cache all prices instead of live computing them. This results in much better performance and allows for much more complex pricing than can be computed on the fly inside queries. Every night a cronjob runs that fills up 21 tables (a table for each possible rental duration). The duration pricing tables contain key,value pairs of arrival date and corresponding computed price for that duration. Optionally you can add a column for group size, resulting in a price per duration, per group size, per arrival date. It takes quite some database records, but if you create indices this is blazing fast.