For an instructor lead, in-depth look at learning SQL click below.
In today’s digital age, virtually managing resources like conference rooms has become necessary for modern organizations. In this blog, we will see how to design a simple Conference Room Booking and Scheduling System using SQL. This will primarily involve creating relevant tables and relationships, adding some seed data, and writing SQL queries for booking and scheduling functionality.
Identifying Necessary Tables
For this system, we need three main tables to store necessary data: rooms, bookings, and users.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100) ); CREATE TABLE rooms ( id INT PRIMARY KEY, room_name VARCHAR(100), capacity INT ); CREATE TABLE bookings ( id INT PRIMARY KEY, user_id INT, room_id INT, start_time DATETIME, end_time DATETIME, FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(room_id) REFERENCES rooms(id) ); |
Adding Some Seed Data
Let’s insert some users, rooms, and a few bookings for our example.
|
1 2 3 4 5 |
INSERT INTO users (id, name, email) VALUES (1, 'John Doe', <a href="mailto:'johndoe@example.com'" >'johndoe@example.com'</a>); INSERT INTO rooms (id, room_name, capacity) VALUES (1, 'Meeting Room 1', 10); INSERT INTO bookings (id, user_id, room_id, start_time, end_time) VALUES (1, 1, 1, '2022-11-18 09:00:00', '2022-11-18 10:00:00'); |
Booking a Room
When a user tries to book a room, we need to check the availability of the room for the given time slot. If the room is available, insert a new row to the bookings table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
DECLARE @user_id INT, @room_id INT, @start_time DATETIME, @end_time DATETIME; SET @user_id = 1; SET @room_id = 1; SET @start_time = '2022-11-18 12:00:00'; SET @end_time = '2022-11-18 13:00:00'; IF NOT EXISTS( SELECT * FROM bookings WHERE room_id = @room_id AND start_time < @end_time AND end_time > @start_time) BEGIN INSERT INTO bookings(user_id, room_id, start_time, end_time) VALUES (@user_id, @room_id, @start_time, @end_time); PRINT 'Booked Successfully!'; END ELSE PRINT 'Room not available for the given time slot.' |
Users can now check availability and book rooms for their meetings. Albeit simple, the system we’ve created demonstrates the power of SQL in managing resources and operations using minimal amount of code.