[RESOLVED] "Availability Calendar" - Days to be assigned as available, how to structure in SQL?

Atomiku

Hey, it's been a long time since posting here. I'm absolutely stuck on how I'm doing to sort out the logic for this piece of functionality a client wants:

We have a Properties database. There will be a calendar where they can mark days down as being available for the property to rent. For instance they may select 12th-25th Feb, 1st May-31st May. How will I store this in MySQL? A one-to-many relationship? I am going to need to: SELECT * FROM Properties WHERE <DAYS AVAILABLE=DATE RANGE SELECTED IN SEARCH BOX>

The italic within the query is pseudo, as I have no idea how I'm going to do this.
To elaborate: when searching for properties, a criteria will be that they will select the arriving and departing date, I need to be able to return properties where ALL days are available to rent within this arriving/departing period. If no dates are available, then I will need to return properties where the available date is near to the arriving/departing period as possible.

I thank you in advance for the help. I only need to know how I will structure the SQL, as I will manage with the front-end and PHP code.

Matt.

Atomiku

Bump. Really need an answer on this one, thanks guys!
Matt.

johanafm

Two tables deals with this information:
- appartments (the actual appartment, whith info on address, current price etc)
- rented (start_date, end_date, actual price)

First off, the reason I list the actual price and current price (both likely to be named simply "price") is that when a user views all appartments, you list appartments.price info. When they rent the appartment, you insert appartments.price into rented.price. If you later change the rental price on the appartment, all current bookings (wether concerning past or future dates, the agreed upon price remains unchanged).

Now, each appartment will have one entry in rented for each period it's unavailable. So when users search for a date range checking for availability, you have to make sure the appartment is NOT listed as unavailable.

There are 2 possible combinations of searched start and end (SS, SE) and rented starts and ends which works, while there are 4 combinations when it doesn't work

These are OK

1. Search period is before (outside) rented period
SS -- SE
		RS -- RE

2. Search period is after (outside) rented period
		SS -- SE
RS --	RE

These are not OK

3. Search period has end date after rental start date (inside range)
SS	--	SE
	RS	--	RE

3. Search period has start after rental start date (inside range)
	SS	--	SE
RS	--	RE

3. Search period has both start and end dates inside range
	SS	--	SE
RS		--		RE

4. Search period has both dates outside range, while there are searched dates inside the range
SS		--		SE
	RS	--	RE

Look at how the searched start date relates to the rented end date, and how the searched end date relates to the rented start date. EVERY time the search period falls within a date range during which the appartment has been rented, the searched start is < the rented end AND the searched end is > the rented start.

What about considering <= and/or >=, that is when one of the days in the searched range is not just less than or greater than, but actually equal to one of the rented days.

Well, if you search for 1-2 may and there is an appartment rented from 2 may - whatever, then the person searching would move in on 1st, and leave around noon the 2nd, giving a few hours to clean the place letting the second person move in at 4PM the 2nd. The same reasoning goes for the start date. That is, in this case the comparisons should be <= and >= respectively.

Should you however always need more than a few hours to inspect and possibly clean the place, so that you can't manage one person departing the same day another arrives, you'd use < and > comparisons.

In the first case, assuming you from time to time need an extra day (or more) to handle repairs or whatever, you can add them to the rental table (also add a specials column or whatever you'd call it)

TABLE: rented
appartment_id	start_date		end_date	specials	client_id	price
1				2012-05-01		2012-05-02	repairs		NULL		0

which means this entry would work just like any other booking when searching for availability. It would however disable the use of FOREIGN KEY (client_id) REFERENCES client(id). You could create a client called repair and set its client_id in this table. Or you could create a second table called maintenance with app_id, start_date, end_date, comment, thus leaving the rented table with only info on the actual rental periods. And yes, this is the way to go... Just showing the detour I usually take in these cases as I discover the needs. But don't get sloppy when designing the schema, you will regret it later since you'd have to deal with special cases in code instead (horrible work).

Atomiku

Hi johanafm, thanks for taking the time to help me here. I've read through your post a couple of times, trying to make sense of it. It seems that if I implement your idea, it would be working backwards to what I am trying to achieve.

Let me elaborate, these 'properties' are actually holiday villas/gites in France. Now, the property owners will simply have a 2012/2013 calendar where they can select individual days where their property is available to rent. They would essentially be selecting date ranges, but the ability is there for selecting a couple of days, or a series of weeks. Your idea works a bit differently to how I envision this system to work. You have a property.price and a rented price, now, the holiday villa owner selects 3 prices. A low, mid and high season. All properties are booked over the phone, email, etc. My system doesn't actually handle that. If a property owner has taken a booking, he/she will just unselect those dates from the availability calendar.

If I stored these days in an array and then encoded into JSON, and stored that in a field, I'd know how to do the coding for it. But the problem here, is when a user is searching for properties. I don't believe it is a good idea to put such logic in PHP. It would break pagination, etc. Know what I mean?

So, here is what I need:

User selects to search properties where he will stay from 2nd June - 10th July.
foreach($stay_dates RANGE as $day) { $conditions .= " AND properties_dates.time = $day"; }
SELECT * FROM properties JOIN properties_dates WHERE $conditions;

Excuse the fact that the above code is totally pseudo and would hardly work, but you see here that I may need PHP to help with the query. Maybe I take the date range that the user has defined, and use that to build the conditions. 2nd June - 10th July becomes an array of 8 timestamps perhaps.

johanafm

OK, since you don't have properties available all the time, you will need to add that constraint. For now, forget the initial information.

Since you deal with avilability information, both start date and end date has to be inside an availability range. Since you join a property with its available date range in property_periods, apart from the obvious join condition on property_id, also add join conditions for the dates.

SELECT *
FROM properties p
	JOIN properties_dates pd ON p.id = pd.property_id
		AND @search_start BETWEEN pd.start_date AND pd.end_date
		AND @search_end BETWEEN pd.start_date AND pd.end_date

Note: BETWEEN is inclusive on both lower and upper bound.

Now, if I've understood you correctly, you'd start out with info on dates such as june 1 - aug 31 for a property available for 3 months, which is then possibly split into two periods if the owner tells you he rented it out during july, giving two entries with june 1 - july 1 and aug 1 - aug 31.

Just as I mentioned concerning "maintenance", you could opt to keep the original info, in order to NOT have to split things up in several periods, but rather treat everything as an "unavailability period", wether it's due to the appartment being rented out (by you or by owner), maintenance or anything else. So even if the owner would only rent an appt. from may 1 to july 1 and aug 1 to aug 31, you'd keep availability dates in the appartment info (skip appartment_periods) with may 1 - aug 31, and then change the name of table "rented" to "booked", where you'd keep the same stuff as outlined in my first post

id	appartment_id	start_date	end_date	type			type_id
1	1				2012-07-01	2012-08-01	'owner_booking'	NULL

In the same table, you'd keep adding entries for when you are then one renting out the appartment, thus when client with id 1 rents it for two weeks at beginning of may

id	appartment_id	start_date	end_date	type		type_id
2	1				2012-05-01	2012-05-14	'client'	1

In this case, you would have to programatically ascertain that type_id is properly set when you have type client, which can be done with database triggers. Either way, since you should never ever delete a client from the db (there simply is no use of doing so). Should you ever need to implement a feature of "deleting" a client, simply add an "active" field to the clients table and set it to 0 when you wish to delete a client (thus keeping it in the table), and for any searches or listings where "deleted" clients should not be included, add active=1 to the searches.

The upside of this approach is that you can skip the "appartment_periods" table, no matter how many periods during a year the appartment is available, and from that point on treat any period in the exact same way. Thus, the same form which is used to book appartments for clients can be used to add other unvailable periods. You just add another select box with "types", such as client, owner_reason, maintenance or whatever is needed. Note that this would not be present in an online bookings form available to clients, since the only booking they do is of type client.

And you'd use the approach I described in my first post, with the added constraint

WHERE @search_start BETWEEN appartment.start_date AND appartment.end_date AND
@search_end BETWEEN appartment.start_date AND appartment.end_date

Atomiku

Thanks again. I have delayed implementing the availability calendar+search until Monday now.
But I have read what you have said, and it makes sense. I think this is exactly what I need! Great logic.

I will mark this as resolved for now, but if I encounter any problems, or get stuck while implementing your idea, would it be okay to private message you?

Again, many thanks for taking the time to help.

Matt.

johanafm

Atomiku;11012297 wrote:
I think this is exactly what I need!

Glad I could help. One more thing I came to think of: add a separate table with pricing info and their respective time periods, i.e.

table: pricing
id, price, start_date, end_date

This table would NOT be used to check for availability since that is handled above. This is used to retrieve and display pricing info to the customer, or whomever is viewing the booking form. When a booking is made (after availability check) this would be used to calculate how many time units are inside each price period.

Atomiku;11012297 wrote:
I will mark this as resolved for now, but if I encounter any problems, or get stuck while implementing your idea, would it be okay to private message you?

Do as you wish, but I'd recommend against it. First off I'm here quite infrequently and usually also miss private messages since all I come to do is check the board. Additionally, there are several other frequent users who are at least as skilled as I am and will have no problem at all helping you implement this and/or improve on it.

Also, there may be users to whom the above is not obvious, but are using the board like I did and sometimes still do: Whenever I find a question I can learn something from answering, I read up on what I need, create an implemention of it, and both help someone else as well as learn from the experience. Aside from that, others may benefit from seeing your questions answered as well.

So there are several good reasons to keep it to the board 🙂

However, to avoid the risk of people not reading threads marked as resolved, create a new thread and start by pasting the link to this one in your first post.