How to find database time overlap

I'm creating database to check if Room is available for entered time. And I'm having a problem with writing the query. Query must check Schedule table and Reservation table if there is overlap with entered time. It will be the best that query return TRUE if there is a overlap and FALSE if there isn't. Is this good database structure for that (so I can focus only on query) or I need to restructure database? Please help me.

My database:

CREATE TABLE "Room" (
    "ID" VARCHAR PRIMARY KEY  NOT NULL  UNIQUE,
    "Name" VARCHAR NOT NULL,
    "Location" VARCHAR NOT NULL,
    "Capacity" INTEGER
)

CREATE TABLE "Schedule" (
    "ID" VARCHAR,
    "Day" VARCHAR,
    "start_time" DATETIME,
    "end_time" DATETIME
)

CREATE TABLE "Reservation" (
    "ID" VARCHAR PRIMARY KEY  NOT NULL,
    "Day" VARCHAR NOT NULL,
    " start_time " DATETIME NOT NULL,
    " end_time " DATETIME NOT NULL,
    "User" VARCHAR
)

Answers


Choose the appropriate data type for each column, the "ID" columns better be INT. What do you need the "Day" columns for? There's a date in your "start_time" and "end_time" columns anyway.

Also I don't see a connection between the "Room" table and the other two.

CREATE TABLE "Room" (
    "ID" INT PRIMARY KEY  NOT NULL  UNIQUE,
    "Name" VARCHAR NOT NULL,
    "Location" VARCHAR NOT NULL,
    "Capacity" INT
)

CREATE TABLE "Schedule" (
    ID INT,
    start_time DATETIME,
    end_time DATETIME,
    room_id INT
)

CREATE TABLE "Reservation" (
    "ID" INT PRIMARY KEY  NOT NULL,
    "start_time " DATETIME NOT NULL,
    "end_time " DATETIME NOT NULL,
    "User" VARCHAR /*you might want to make this an INT, too, and put the users in an extra table*/
    , room_id INT
)

I don't know what DBMS you are using, so I also don't know correct syntax, but you better put a foreign key on the room_id columns in Schedule and Reservation tables referencing id column in Room table.

Then you do it like this:

SELECT
*
FROM
Room 
LEFT JOIN Schedule ON Room.id = Schedule.room_id
LEFT JOIN Reservation ON Room.id = Reservation.room_id
WHERE 
(
(Schedule.start_time <= $yourStartTime AND Schedule.end_time > $yourEndTime)
OR
(Reservation.start_time <= $yourStartTime AND Reservation.end_time > $yourEndTime)
) 
AND Room.id = $yourRoomId

This query returns something when the room is full and nothing / NULL when the room is free.


Need Your Help

not using partial mocking? do they also mean in web-app?

unit-testing testing web-applications mocking

Im learning Mockito and in chapter 16 they say you should not use partial mocking in new system.

How do HTTP proxy caches decide between serving identity- vs. gzip-encoded resources?

http caching proxy reverse-proxy content-encoding

An HTTP server uses content-negotiation to serve a single URL identity- or gzip-encoded based on the client's Accept-Encoding header.