MySQL – Best Way to Put Date Range in a Table

database-designdatedatetimeMySQL

I'm designing a system which is basically a booking engine. it uses separate table to store room data and separate table to store it's availability information (available date(s) ). I'm stuck in designing at the availability table.

This is the structure in my rooms table,

room_id
room_type
room_name etc...

This is my current availability table structure

room_id (int)
week_id (int)
date (datetime)
availability (boolean)

is there any better way to achieve this?please help. even the tiniest advice will be greatly appreciated

Best Answer

Note: not a MySQL guy, I use MS SQL. I would try to focus less on if a room is available at the start, and try to focus on marking the dates the room is reserved. If you use a table that stores reservations;

CREATE TABLE [Reservations] (
ReservationID INT NOT NULL,
RoomID INT NOT NULL,
StartDate SmallDate,      --first day of reservation
Duration INT)             --number of days being reserved

Then when you want to see what rooms are available for a day, you can search for what rooms are being used that day (using the startdate and duration columns) and get the inverse. This also frees you up from trying to allocate space for each possible reservation slot, but just add a record into the reservations table when they are reserved.

Related Solutions

Mysql – Database design for dynamic Meta fields of an entity that shall be queried intensively

To my knowledge there are these 2 ways you can go:

If you want performance, simpler queries, easier programming, then you should make a second table with your ID as foreign key and make there a column for each of your properties that you want to store. For any new attribute you would have to change the database schema, what might be a major drawback.
The above won't work, if you need flexibility in your database schema (because your meta properties are not known in advance or are point to change), but it will add to your code and queries complexity (depending on your framework/application) and also lead to poor performance compared to the first alternative. Poor performance can be compensated to a certain extent with more hardware, but that's expensive.

It's a trade off between the two. You must judge your application scenario and decide what you need most and what is a absolute no-go. Then decide on the remaining criteria what you want to use.

I have seen and used both ways I mentioned above, and must say the second one worked better for me (didn't have high volume querying though), even if it is a antipattern from a SQL / database normalization point of view. However, we aren't payed for nice SQL database designs, but for working applications.

Mysql – Multiple left joins or subquery

This is my best shot without seeing any table structures

SELECT
    user_runs.userID,
    user_runs.runID,
    `createdBy`,
    `username`,
    DATE_FORMAT(`dateCreated`,'%d-%m-%Y') `dateCreated`, 
    COUNT(DISTINCT runFavouriteID) AS favourite,
    DATE_FORMAT(MAX(`runDate`),'%d-%m-%Y') AS `lastRan`,
    MIN(`runTime`) AS `fastestTime`
FROM
    (SELECT * FROM `user_runs` WHERE userID=4) user_runs
    INNER JOIN `runs` ON user_runs.runID = runs.runID
    INNER JOIN `users` ON users.userID = runs.createdBy
    LEFT OUTER JOIN `user_run_times` ON user_run_times.runID = user_runs.runID 
    LEFT OUTER JOIN `run_favourites` ON run_favourites.runID = user_runs.runID
GROUP BY
    user_runs.runID,`createdBy`,`username`,`dateCreated`;

You will need these indexes

ALTER TABLE user_runs ADD INDEX userID_runID_ndx (userID,runID);
ALTER TABLE runs      ADD INDEX runID_createdBy_ndx (runID,createdBy);
ALTER TABLE runs      ADD INDEX createdBy_runID_ndx (createdBy,runID);

Best Answer

Related Solutions

Mysql – Database design for dynamic Meta fields of an entity that shall be queried intensively

Mysql – Multiple left joins or subquery

Related Question