0
\$\begingroup\$

I want to create a database which gives an admin user ability to create schedule for other users to book from. This is second edition of this database schema.

I would like to know if there are any structure / logic issues with this schema. What would you change and why or if you see any potential problems regarding its original purpose.

What would you use for storing price? I know that FLOAT and DOUBLE are not precise datatypes (estimate) There fore we should not use them to store price. So we can choice from DECIMAL ora INT.

Decimal seems to be the good choice however I have seen some payment gateways to force to use INT to store their translation values (so if the price was 11,99 you send to their API 1199)

10.4.19-MariaDB

-- TABLE Services --
CREATE TABLE Services(
    id INT AUTO_INCREMENT PRIMARY KEY,
    service VARCHAR(255) NOT NULL,
    length_in_min INT NOT NULL DEFAULT 20,
    capacity INT NOT NULL DEFAULT 1
);

INSERT INTO Services (service, length_in_min, capacity) 
VALUES 
    ('ANY', 20, 1), -- if ANY allow for all service apointments
    ('USG', 30, 1), -- if USG allow aonly USG apointments
    ('VISION', 10, 1); -- if VISION allow aonly VISION apointments

SELECT * FROM Services;

-- TABLE Staff --
CREATE TABLE Staff(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(255) NOT NULL
);

INSERT INTO Staff (name, email, phone) 
VALUES
    ("dr Lee", "somedr@email.com", "888 888 888");

SELECT * FROM Staff;

-- TABLE Locations --
CREATE TABLE Locations(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    room VARCHAR (255) DEFAULT 'Main',
    address1 VARCHAR(255) NOT NULL,
    address2 VARCHAR(255) NOT NULL,
    post VARCHAR(6) NOT NULL
);

INSERT INTO Locations (name, address1, address2, post) 
VALUES
    ("Pet Clinick", "Sezam Streen 17", "London", "06-100");

SELECT * FROM Locations;

-- TABLE Schedules --
CREATE TABLE Schedules(
    id INT AUTO_INCREMENT PRIMARY KEY,
    day INT NOT NULL,
        CONSTRAINT chk_if_days_in_range CHECK(day BETWEEN 1 AND 7),
    location_id INT NOT NULL REFERENCES Locations(id),
    staff_id INT NOT NULL REFERENCES Staff(id),
    open TIME NOT NULL,
    close TIME NOT NULL,
        CONSTRAINT chk_open_close_order CHECK(close > open),
    created_at TIMESTAMP DEFAULT NOW(),
    modified_at TIMESTAMP DEFAULT NOW(),
        CONSTRAINT chk_edit_timestamp_order CHECK(modified_at >= created_at),
    starts_at DATETIME NOT NULL DEFAULT NOW(),
    ends_at DATETIME NOT NULL DEFAULT '9999-01-01 00:00:00', 
        CONSTRAINT chk_booking_datetime_order CHECK(ends_at >= starts_at)
);

INSERT INTO Schedules (staff_id, location_id, day, open, close) 
VALUES
    (1, 1, 1, '10:00:00', '18:00:00'),
    (1, 1, 3, '08:30:00', '16:00:00'),
    (1, 1, 4, '08:30:00', '16:00:00')
;

SELECT id as shedule_id, day, open, close, starts_at, ends_at FROM Schedules;

-- TABLE Schedules_Service --
CREATE TABLE Schedules_Service(
    id INT AUTO_INCREMENT PRIMARY KEY,
    service_id INT NOT NULL REFERENCES Services(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    schedules_id INT NOT NULL REFERENCES Schedules(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    limit_per_shedule INT NOT NULL DEFAULT 999,
    price DECIMAL(10,2) NOT NULL
);

INSERT INTO Schedules_Service (service_id, schedules_id, price, limit_per_shedule)
VALUES
    (1, 1, 60.00, 10),
    (2, 1, 60.00, 10)
;

-- TABLE Clients --
CREATE TABLE Clients(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(255) NOT NULL,
    legals BOOLEAN DEFAULT TRUE
);

INSERT INTO Clients (name, email, phone) 
VALUES  
    ("Wiktor", "some@email.com", "000 000 000"),
    ("John", "john@email.com", "111 111 111")
;

SELECT * FROM Clients;

-- TABLE Appointments --
CREATE TABLE Appointments(
    id INT AUTO_INCREMENT PRIMARY KEY,
    service_id INT NOT NULL REFERENCES Services(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    client_id INT NOT NULL REFERENCES Clients(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    -- shedule_id ? Schedule id ?
    created_at TIMESTAMP DEFAULT NOW(),
    modified_at TIMESTAMP DEFAULT NOW(),
    starts_at DATETIME NOT NULL,
    ends_at DATETIME NOT NULL,
    approved_by_client BOOLEAN NOT NULL DEFAULT FALSE
);
\$\endgroup\$
2
\$\begingroup\$

Reserved keywords

Be careful with overly generic keywords, there is a good chance that they are reserved keywords. Name, Open, Close are reserved keywords in MySQL.

Accordingly, I suggest that you you rename some objects from 'name' to for example: location_name, staff_name.

VARCHAR

VARCHAR (255) sounds like a default value suggested by a design tool. For an E-mail address the need for 255 characters is dubious. For a phone number this is definitely oversized. Even in international format I don't think it would exceed 20 characters.

Think about the UI and what kind of values you are willing to accept, even while keeping room for flexibility. Then model your database along reasonable, real-world expectations.

This "design" choice may (or may not have) an impact on performance, depending on a number of factors. Discussion: Are there disadvantages to using a generic varchar(255) for all text-based fields? and MySQL - varchar length and performance.

Case

I advise that you use full lower case for all object names. Keep in mind that on Linux systems, the filesystem is usually case-sensitive and that applies to objects such as tables.

Indexes

What about indexes ? If your tables keep on growing, queries are going to be take longer and longer because they are not optimized in any way. Think about the JOINs, and what fields you are going to do lookups against.

UNIQUE

You have a UNIQUE constraint on the E-mail address, which makes sense. However, in the real world, you might find yourself in a situation where two people (eg spouses) share the same E-mail address. So is this a good idea ? This may be an edge case but I would relax the requirement. The point is that the address could be a search criterion but not the sole customer identifier.

Misc.

In the table clients, it would make sense to split the name in two distinct columns: family name, surname, even if that table is clearly minimalist. For instance, you might want to search/sort on family name only. Also, the front-end UI will probably use a form or some kind of datagrid with columns to render the data, and separating the two fields makes sense. Not doing so could be a source of ambiguity, because the distinction between surname and family name may not always be clear.

Datetime

In table Staff, you defined the field dob as:

 dob YEAR NOT NULL

This not consistent with your insert example ('1988-01-01'). You could simply use the DATE datatype.

Regarding the choice of datatypes: table Appointments uses both TIMESTAMP and DATETIME but they are not equivalent. Check the link above for details. Be aware that TIMESTAMP has the 2K38 problem:

TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

So any program that relies on it is not future-proof. I recommend that you use datetime and for auto-updading last changed time: Automatic Initialization and Updating for TIMESTAMP and DATETIME

What is the justification for:

ends_at DATETIME NOT NULL DEFAULT '9999-01-01 00:00:00'

in table Schedules ? Just let it null by default, then your program will know the event is open-ended.

Last but not least, assuming that you are based in the US and taking into account the possibility that your program could be deployed at multiple locations nationwide (even in a remote future), you might want to consider using timezone-aware datatimes. While this adds initial complexity this also provides flexibility and adaptability. After all a datetime without context is meaningless.

Number formatting

In that same table you store a phone number like: '888 888 888'. Recommendation: store numbers without spaces or artificial characters. Let your program handle the formatting of numbers. I would maybe add the country code in case you have foreign customers. Or you could decide to store all numbers in international format eg: +18004567890.

In table Locations:

post VARCHAR(6) NOT NULL

Is this the zip code ? Then unsigned int zero fill is a possible option. But this is only good for US addresses. For a British postcode this would be a bit short.

Charset

What is the charset you are using ? Will your application handle accented characters smoothly ? Will client José Peños be saved to the database without glitches ?

\$\endgroup\$
4
  • \$\begingroup\$ Per mariadb.com/kb/en/identifier-names reserved words can be escaped, which is an alternative to renaming. \$\endgroup\$
    – Reinderien
    Jul 12 at 19:53
  • \$\begingroup\$ Re. Just let [ends_at] null by default - there are some contexts in which a far-future date will simplify queries over having a nullable column. For instance, if you're doing a naive comparison of the current date to ends_at, the expression would evaluate to false in the far-future case and null in the nullable case, requiring a separate coalesce. \$\endgroup\$
    – Reinderien
    Jul 12 at 19:56
  • \$\begingroup\$ Re. it would make sense to split the name in two distinct columns: family name, surname, even if that table is clearly minimalist - this is deeply problematic. It's an understandable reflex for people used to Western culture where names have clear first and last components, but there are many cultures where this is not remotely true. Even if the database were written to serve one well-known country such as Canada where that's the norm, there are many humans outside of the norm that come from other places. The single-string name field is sensible. \$\endgroup\$
    – Reinderien
    Jul 12 at 19:59
  • \$\begingroup\$ read e.g. uxbooth.com/articles/… \$\endgroup\$
    – Reinderien
    Jul 12 at 20:09
1
\$\begingroup\$
  • Your -- TABLE Services comment is redundant and can be deleted, along with others of its kind
  • Your pseudo-inline syntax used to name your constraints is.. fine I guess? I don't usually place much importance on naming constraints, but this is one way to do it
  • You used to have on delete and on cascade clauses everywhere. That was the right thing to do, and omitting them is somewhat risky. In MariaDB the default is not cascade as you were using, but rather restrict. This is a reasonable default (more reasonable IMO than Postgres whose default is "no action"), but it is not equivalent to what you were using in the foreign keys of your first question. Aside: PostgreSQL puts a great deal of effort into being standards-compliant, so I would expect that its implementation is more standard - but I have not verified this.
  • The spelling of "schedule" has not changed in the last 48 hours.
  • Just as in the last question, you still must not hard-code IDs. Do not hard-code 1 for your staff ID, or pass any other numeric values for IDs as literals like this. This is guaranteed to break idempotence and validity of your DML under a variety of common situations. This goes beyond standards adherence and best-practices to being straightforwardly incorrect, and the solutions are trivial enough: either select based on the other columns that you know e.g. staff name, or somewhat more robustly, use a returning clause in your insert to just give you the ID you want, and put it in a variable.
  • I'm pleased to see that you used decimal for a monetary quantity.
\$\endgroup\$
2
  • \$\begingroup\$ Reinderien thank you for your contribution I do appreciate your help. Any comments in database will be removed at the very end of structuring this database, when It will be close to production ready. Regrading 5th point. The hardcoding IDs are only for quick demo purpose. I do not intend to use it this way in production. I am aware that I need to query the table to get the ID before making an insert to another table as fk. I have also seen the INT to be used to store the price data. Wasn't sure which approach to should i choose. \$\endgroup\$
    – DevWL
    Jul 12 at 16:08
  • \$\begingroup\$ I understand the urge to make a quick demo, but: whether this is for school, work or open-source contribution, you want to show your best self. Demonstrating correct use of auto-generated columns is very important, so "easy and temporarily very wrong" is not a good replacement for "easy and always right". \$\endgroup\$
    – Reinderien
    Jul 12 at 16:13
0
\$\begingroup\$

Edit II - based on Reinderien user suggestions

Schema UPDATE
  • spelling of "schedule"
  • guidelines on how to properly INSERT foreign key ID to a Table

+ small tweaks adding UNIQUE to some tables (Clients.email and Services.name) to use those columns to SELECT unique id.

See general visualisation [here] enter image description here

Ps. Should I still need id INT AUTO_INCREMENT PRIMARY KEY if I can use his email as a way retrieve unique data row?

The database look now as fallow:

CREATE TABLE Services(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    length_in_min INT NOT NULL DEFAULT 20,
    capacity INT NOT NULL DEFAULT 1
);

INSERT INTO Services (name, length_in_min, capacity) 
VALUES 
    ('ANY', 20, 1), -- allow any type appointment
    ('USG', 30, 1), -- allow only USG type appointments
    ('VISION', 10, 1)-- allow only VISION type appointments
;

SELECT * FROM Services;

CREATE TABLE Staff(
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    surname VARCHAR(255) NOT NULL,
    spec VARCHAR(255) NOT NULL,
    dob YEAR NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(255) NOT NULL
);

INSERT INTO Staff (title, name, surname, spec, dob, email, phone) 
VALUES
    ('dr', 'John', 'Lee', 'vet', '1988-01-01', 'somedr@email.com', '888 888 888');

SELECT * FROM Staff;

CREATE TABLE Locations(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    room VARCHAR (255) DEFAULT NULL,
    address1 VARCHAR(255) NOT NULL,
    address2 VARCHAR(255) NOT NULL,
    post VARCHAR(6) NOT NULL
);

INSERT INTO Locations (name, address1, address2, post) 
VALUES
    ('Pet Clinick', 'Sezam Streen 17', 'London', '06-100');

SELECT * FROM Locations;

CREATE TABLE Schedules(
    id INT AUTO_INCREMENT PRIMARY KEY,
    day INT NOT NULL,
        CHECK(day BETWEEN 1 AND 7),
    location_id INT NOT NULL REFERENCES Locations(id),
    staff_id INT NOT NULL REFERENCES Staff(id),
    open TIME NOT NULL,
    close TIME NOT NULL,
        CHECK(close > open),
    created_at TIMESTAMP DEFAULT NOW(),
    modified_at TIMESTAMP DEFAULT NOW() ON UPDATE NOW(),
        CHECK(modified_at >= created_at),
    starts_at DATETIME NOT NULL DEFAULT NOW(),
    ends_at DATETIME NOT NULL DEFAULT '9999-01-01 00:00:00', 
        CHECK(ends_at >= starts_at)
);

INSERT INTO Schedules (staff_id, location_id, day, open, close) 
VALUES
    (
        (SELECT id FROM Staff st WHERE st.name = 'John' AND st.surname = 'Lee' AND spec = 'vet'),
        (SELECT id FROM Locations l WHERE l.name = '' and l.room = 'Main'), 
        1, 
        '10:00:00', 
        '18:00:00'
    ), -- query others the same way
    (1, 1, 2, '10:00:00', '18:00:00'),
    (1, 1, 3, '10:00:00', '18:00:00'),
    (1, 1, 4, '10:00:00', '18:00:00'),
    (1, 1, 5, '10:00:00', '18:00:00'),
    (1, 1, 6, '08:30:00', '16:00:00'),
    (1, 1, 7, '08:30:00', '16:00:00')
;

SELECT id as schedule_id, day, open, close, starts_at, ends_at FROM Schedules;

CREATE TABLE Schedules_Service(
    id INT AUTO_INCREMENT PRIMARY KEY,
    service_id INT NOT NULL REFERENCES Services(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    schedules_id INT NOT NULL REFERENCES Schedules(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    limit_per_schedule INT NOT NULL DEFAULT 999,
    price DECIMAL(10,2) NOT NULL
);

INSERT INTO Schedules_Service (service_id, schedules_id, price, limit_per_schedule)
VALUES
    (
        (SELECT id FROM Services WHERE name = 'ANY'),
        (SELECT id FROM Schedules WHERE id = 1),
        60.00,
        10
    ), -- query others the same way
    (2, 1, 60.00, 10)
;

CREATE TABLE Clients(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(255) NOT NULL,
    legals BOOLEAN DEFAULT TRUE
);

INSERT INTO Clients (name, email, phone) 
VALUES  
    ('Wiktor', 'some@email.com', '000 000 000'),
    ('John', 'john@email.com', '111 111 111')
;

SELECT * FROM Clients;

CREATE TABLE Appointments(
    id INT AUTO_INCREMENT PRIMARY KEY,
    service_id INT NOT NULL REFERENCES Services(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    client_id INT NOT NULL REFERENCES Clients(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    -- schedule_id ? Schedule id ?
    created_at TIMESTAMP DEFAULT NOW(),
    modified_at TIMESTAMP DEFAULT NOW(),
    starts_at DATETIME NOT NULL,
    ends_at DATETIME NOT NULL,
    approved_by_client BOOLEAN NOT NULL DEFAULT FALSE
    -- UNIQUE KEY make_booking_unique (starts_at, ends_at, ...)
);

INSERT INTO Appointments (client_id, service_id, starts_at, ends_at) 
VALUES 
    (   
        (SELECT id FROM Clients WHERE email = 'some@email.com'), 
        (SELECT id FROM Services WHERE name = 'USG'), 
        '2021-07-10 11:00:00', 
        '2021-07-10 11:30:00'
    ), -- query others the same way
    (1, 2, '2021-07-10 12:00:00', '2021-07-10 12:30:00'), -- 2 is USG name
    (1, 2, '2021-07-10 13:00:00', '2021-07-10 13:30:00'), -- 2 is USG name
    (1, 1, '2021-07-10 13:30:00', '2021-07-10 14:00:00') -- 1 is any name
;

SELECT id, starts_at, ends_at  FROM Appointments ORDER BY starts_at;
\$\endgroup\$
0
\$\begingroup\$

Edit III - based on Anonymous user suggestions:

+ escaped reserved keywords
+ tables are named now lower case
+ changed YEAR to DATE
+ used shorter VARCHAR (to avoid large temp tables - thanks for that)

+ moved some stuff from schedules to services_schedules

+ added conditional insert based on existence in schedule table and no existence in appointments table (under development)
- other in progress

if you would like to propose some changes you can do it on GitHub here

DROP DATABASE IF EXISTS bookings;
CREATE DATABASE bookings CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

USE bookings;

CREATE TABLE services(
    id INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(30) NOT NULL UNIQUE,
    length_in_min INT NOT NULL DEFAULT 20,
    capacity INT NOT NULL DEFAULT 1
);

INSERT INTO services (`name`, length_in_min, capacity) 
VALUES 
    ('ANY', 20, 1), -- allow any type appointment
    ('USG', 30, 1), -- allow only USG type appointments
    ('VISION', 10, 1)-- allow only VISION type appointments
;

SELECT * FROM services;

CREATE TABLE staff(
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(20) NOT NULL,
    `name` VARCHAR(30) NOT NULL,
    surname VARCHAR(60) NOT NULL,
    spec VARCHAR(60) NOT NULL,
    dob DATE NOT NULL,
    email VARCHAR(50) NOT NULL,
    phone VARCHAR(16) NOT NULL
);

INSERT INTO staff (title, `name`, surname, spec, dob, email, phone) 
VALUES
    ('dr', 'John', 'Lee', 'vet', '1988-01-01', 'somedr@email.com', '888 888 888')
;

SELECT * FROM staff;

CREATE TABLE locations(
    id INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(30) NOT NULL,
    room VARCHAR (20) DEFAULT NULL,
    address1 VARCHAR(50) NOT NULL,
    address2 VARCHAR(50) NOT NULL,
    post VARCHAR(8) NOT NULL
);

INSERT INTO locations (`name`, address1, address2, post) 
VALUES
    ('Pet Clinick', 'Sezam Streen 17', 'London', '06-100')
;

SELECT * FROM locations;

CREATE TABLE schedules(
    id INT AUTO_INCREMENT PRIMARY KEY,
    `day` INT NOT NULL,
        CHECK(`day` BETWEEN 1 AND 7),
    location_id INT NOT NULL REFERENCES locations(id),
    `open` TIME NOT NULL,
    `close` TIME NOT NULL,
        CHECK(close > open),
    created_at TIMESTAMP DEFAULT NOW(),
    modified_at TIMESTAMP DEFAULT NOW() ON UPDATE NOW(),
        CHECK(modified_at >= created_at),
    starts_at DATETIME NOT NULL DEFAULT NOW(),
    ends_at DATETIME DEFAULT NULL,
        CHECK(ends_at >= starts_at)
);

INSERT INTO schedules (location_id, `day`, `open`, `close`, starts_at) 
VALUES
    (
        (SELECT id FROM locations lo WHERE lo.name = 'Pet Clinick'), 
        1, 
        '10:00:00', 
        '18:00:00',
        '2021-01-01 00:00:00'
    ), -- query others the same way
    (1, 2, '10:00:00', '18:00:00', '2021-01-01 00:00:00'),
    (1, 3, '10:00:00', '18:00:00', '2021-01-01 00:00:00'),
    (1, 4, '10:00:00', '18:00:00', '2021-01-01 00:00:00'),
    (1, 5, '10:00:00', '18:00:00', '2021-01-01 00:00:00'),
    (1, 6, '08:30:00', '16:00:00', '2021-01-01 00:00:00'),
    (1, 7, '08:30:00', '16:00:00', '2021-01-01 00:00:00')
;

SELECT sc.id, sc.day, sc.open, sc.close, sc.starts_at, sc.ends_at FROM schedules sc;

CREATE TABLE schedules_service(
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT NOT NULL REFERENCES staff(id),
    service_id INT NOT NULL REFERENCES services(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    schedules_id INT NOT NULL REFERENCES schedules(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    starts_at TIME NOT NULL,
    ends_at TIME NOT NULL
        CHECK(ends_at >= starts_at),
    limit_per_schedule INT NOT NULL DEFAULT 999,
    price DECIMAL(10,2) NOT NULL
);

INSERT INTO schedules_service (staff_id, service_id, schedules_id, starts_at, ends_at, price, limit_per_schedule)
VALUES
    (
        (SELECT id FROM staff st WHERE st.name = 'John' AND st.surname = 'Lee' AND spec = 'vet'),
        (SELECT id FROM services s WHERE s.name = 'USG'),
        (SELECT id FROM schedules s WHERE s.day = 1),
        '12:00:00',
        '12:30:00',
        60.00,
        10
    ), -- query others the same way
    (1, 2, 2,'13:00:00', '13:30:00', 60.00, 10)
;

CREATE TABLE clients(
    id INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(30) NOT NULL,
    email VARCHAR(60) NOT NULL UNIQUE,
    phone VARCHAR(16) NOT NULL,
    legals BOOLEAN DEFAULT TRUE
);

INSERT INTO clients (`name`, email, phone) 
VALUES  
    ('Wiktor', 'some@email.com', '000 000 000'),
    ('John', 'john@email.com', '111 111 111')
;

SELECT * FROM clients;

CREATE TABLE appointments(
    id INT AUTO_INCREMENT PRIMARY KEY,
    service_id INT NOT NULL REFERENCES services(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    client_id INT NOT NULL REFERENCES clients(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    -- schedule_id ? Schedule id ?
    created_at TIMESTAMP DEFAULT NOW(),
    modified_at TIMESTAMP DEFAULT NOW() ON UPDATE NOW(),
        CHECK(modified_at >= created_at),
    starts_at DATETIME NOT NULL,
    ends_at DATETIME NOT NULL,
    approved_by_client BOOLEAN NOT NULL DEFAULT FALSE
    -- UNIQUE KEY make_booking_unique (starts_at, ends_at)
);

-- INSERT INTO appointments (client_id, service_id, starts_at, ends_at) 
-- VALUES 
--     (   
--         (SELECT id FROM clients WHERE email = 'some@email.com'), 
--         (SELECT id FROM services WHERE name = 'USG'), 
--         '2021-07-10 11:00:00', 
--         '2021-07-10 11:30:00'
--     ), -- query others the same way
--     (1, 2, '2021-07-10 12:00:00', '2021-07-10 12:30:00'), -- 2 is USG name
--     (1, 2, '2021-07-10 13:00:00', '2021-07-10 13:30:00'), -- 2 is USG name
--     (1, 1, '2021-07-10 13:30:00', '2021-07-10 14:00:00') -- 1 is any name
-- ;

SET @s := '2021-07-12 12:00:00';
SET @e := '2021-07-12 12:30:00';

SELECT( WEEKDAY(@s));
SELECT(TIME(@s));
SELECT * FROM schedules sc LEFT JOIN schedules_service ss ON ss.schedules_id = sc.id;

SELECT ss.id, sc.day FROM schedules sc LEFT JOIN schedules_service ss ON ss.schedules_id = sc.id
            WHERE 
                WEEKDAY(@s) + 1 = sc.day 
                AND TIME(@s) = ss.starts_at 
                AND TIME(@e) = ss.ends_at;

INSERT INTO appointments
        (client_id, service_id, starts_at, ends_at) 
    SELECT 
        (SELECT id FROM clients c WHERE c.email = 'some@email.com'),
        (SELECT id FROM services s WHERE s.name = 'ANY'),
        @s, 
        @e
    WHERE NOT EXISTS -- search for colisions if there is colision row returned no insert will be run
        (
            SELECT id FROM appointments
            WHERE 
                (starts_at <= @s AND @s < ends_at)
                OR (starts_at < @e AND @e <= ends_at)
                OR (@s <= starts_at AND starts_at < @e)
            LIMIT 1 
        )
    AND EXISTS -- see if this timeslot is available for bookings
        (
            SELECT ss.id, sc.day FROM schedules sc LEFT JOIN schedules_service ss ON ss.schedules_id = sc.id
            WHERE 
                (service_id = (SELECT id FROM services s WHERE s.name = 'USG') OR service_id = (SELECT id FROM services s WHERE s.name = 'ANY'))
                AND WEEKDAY(@s) + 1 = sc.day
                AND @s >= sc.starts_at  
                AND TIME(@s) = ss.starts_at 
                AND TIME(@e) = ss.ends_at
        )
    AND (1=1) -- CHECK FOR HOLIDAYS AND OFF DAYS
;
    
SELECT a.id, c.name, s.name, a.starts_at, a.ends_at FROM appointments a LEFT JOIN services s ON a.service_id = s.id LEFT JOIN clients c ON a.client_id = c.id;
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.