MySQL - Find unused time slots in a schedule

Question

I have been search for a solution for a similar case. I have an appointment app where users can book doctors based on their available time slot. I have created a time slot table and booking table. Where am not sure is, if a doctor's time slot is taken for a particular date how do I ensure another user does not pick the same time slot for that same date.

CREATE TABLE `time_slots`  (
    `slot_id` int NOT NULL AUTO_INCREMENT,
    `package_key` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
    `dr_token` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
    `start_time` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
    `end_time` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
    `weekday` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
    `s_time_key` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
    `status` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
    `created_at` datetime(0) NULL DEFAULT NULL,
    `updated_at` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
    PRIMARY KEY (`slot_id`) USING BTREE
)

CREATE TABLE `appointments`  (
    `appointment_id` int NOT NULL AUTO_INCREMENT,
    `patient_token` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
    `doctor_token` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
    `time_slot` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
    `start_time` datetime(0) NULL DEFAULT NULL,
    `end_time` datetime(0) NULL DEFAULT NULL,
    `time_slot_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
    `status` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
    `auth_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
    `confirmed` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
    `invoice_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
    `created_at` datetime(0) NULL DEFAULT NULL,
    `updated_at` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
    PRIMARY KEY (`appointment_id`) USING BTREE
)

why time_slot_id in appointments is varchar and in time_slots table is an int? — Joe Taras, Dec 22 '20 at 15:50
Check if an entry for that time slot already exists and don't store if it does. — El_Vanja, Dec 22 '20 at 15:50

score 1 · Answer 1 · answered Dec 22 '20 at 16:09

You can select the free time slots in a simple query like for example:

select * from time_slots s
left join appointments a on s.slot_id = a.time_slot_id
where a.appointment_id is null

And allow the user to only select one of these slots.

To be on the safe side, you can add a unique key on the time_slot_id column in the appointments table - this way you will not be able to insert two appointments for the same slot.

I advise not to use datetime type, just use unsigned int and save timestamps.

Thank you for your comment. It works fine only if I add date as a condition it does return the desired results — Ricchard, Dec 22 '20 at 22:30

score 0 · Answer 2 · answered Dec 22 '20 at 16:17

Check out this thread, I think it may be asking the same thing as you are: mysql: how put two columns unique in two tables at once

You can also solve this by using a SELECT statement to check for the time slot the user is requesting and make sure an empty result set is returned from that query before performing the insert

MySQL - Find unused time slots in a schedule

2 Answers2