CODEWITHSUNDEEP
phpmysqlperformance
C. Ovidiu
C. Ovidiu

Reputation: 1134

MySql: saving date ranges VS saving single day

I am currently working on a simple booking system and I need to select some ranges and save them to a mysql database.

The problem I am facing is deciding if it's better to save a range, or to save each day separately.

There will be around 500 properties, and each will have from 2 to 5 months booked.

So the client will insert his property and will chose some dates that will be unavailable. The same will happen when someone books a property.

I was thinking of having a separate table for unavailable dates only, so if a property is booked from 10 may to 20 may, instead of having one record (2016-06-10 => 2016-06-20) I will have 10 records, one for each booked day.

I think this is easier to work with when searching between dates, but I am not sure.

Will the performance be noticeable worse ?

Should I save the ranges or single days ?

Thank you

Upvotes: 1

Views: 147

Answers (2)

Rahal Danthanarayana
Rahal Danthanarayana

Reputation: 748

This is too much for a comment,So I will leave this as an answer

So the table's primary key would be the property_id and the Date of a particular month. I don't recommend it.Because think of a scenario when u going to apply this logic to 5 or 10 years system,the performance will be worse.You will get approximately 30*12*1= 360 raws for 1 year.Implement a logic to calculate the duration of a booking and add it to table against the user.

Upvotes: 1

Barry
Barry

Reputation: 3318

I would advise that all "events" go into one table and they all have a start and end datetime. Use of indexes on these fields is of course recommended.

The reasons are that when you are looking for bookings and available events - you are not selecting from two different tables (or joining them). And storing a full range is much better for the code as you can easily perform the checks within a SQL query and all php code to handle events works as standard for both. If you only store one event type differently to another you'll find loads of "if's" in your code and find it harder to write the SQL.

I run many booking systems at present and have made mistakes in this area before so I know this is good advice - and also a good question.

Upvotes: 2

Related Questions