Дизайн базы данных встреч — Схема

Как бы вы подошли к созданию базы данных для приема на работу?

Вот что я до сих пор придумал. Дайте мне знать, если есть лучший способ. По сути, админ должен уметь составлять список сервисов, график работы. На основании этих данных Клиент должен иметь возможность записаться на прием в определенные дни и часы.

CREATE TABLE Services(
    id integer NOT NULL auto_increment,
    service varchar(255) NOT NULL,
    length_in_min integer NOT NULL DEFAULT 20,
    capacity integer NOT NULL DEFAULT 1,
PRIMARY KEY(id));

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


CREATE TABLE Shedules(
    id integer NOT NULL auto_increment,
    day integer NOT NULL,
    open time NOT NULL,
    close time NOT NULL,
    service_id integer NOT NULL DEFAULT 1,
    createdat timestamp DEFAULT NOW(),
    modifiedat timestamp DEFAULT NOW(),
    starts_from datetime NOT NULL DEFAULT NOW(),
    ends_at datetime NOT NULL DEFAULT '9999-01-01 00:00:00',
PRIMARY KEY(id),
FOREIGN KEY (service_id) REFERENCES Services(id)
ON DELETE CASCADE
ON UPDATE CASCADE);

INSERT INTO Shedules(service_id, day, open, close) values(1, 1, '10:00:00', '18:00:00');
INSERT INTO Shedules(service_id, day, open, close) values(2, 3, '08:30:00', '16:00:00');
INSERT INTO Shedules(service_id, day, open, close) values(3, 4, '08:30:00', '16:00:00');

SELECT sh.id as shedule_id, sh.day, sh.open, sh.close, sh.service_id, sh.starts_from, sh.ends_at, se.id as service_id, se.service, se.length_in_min 
FROM Shedules sh 
LEFT JOIN Services se 
ON sh.service_id = se.id;

CREATE TABLE Clients(
    id integer NOT NULL auto_increment,
    name varchar(255) NOT NULL,
    email varchar(255) NOT NULL,
    phone varchar(255) NOT NULL,
    legals BOOLEAN DEFAULT TRUE,
PRIMARY KEY(id));

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


CREATE TABLE Appointments(
    id integer NOT NULL auto_increment,
    service_id int NOT NULL, -- service int
    client_id int NOT NULL, -- client int
    createdat timestamp DEFAULT NOW(),
    modifiedat timestamp DEFAULT NOW(),
    starts datetime NOT NULL,
    ends datetime NOT NULL,
    paid_online BOOLEAN NOT NULL DEFAULT FALSE,
    approved_by_client BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY(id),
FOREIGN KEY (service_id) REFERENCES Services(id)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (client_id) REFERENCES Clients(id)
ON DELETE CASCADE
ON UPDATE CASCADE);

// insert in to appointments if ...

• Это вопрос личных предпочтений, но ничто в стандарте SQL не требует прописных букв, и я считаю, что строчные буквы более разборчивы.
• В столбцах первичного ключа замените NOT NULL со встроенным primary key объявление ограничения, которое подразумевает not null в любом случае
• Переместите ограничения внешнего ключа во встроенные
• length_in_min может быть числом с плавающей запятой, если вы хотите представить дробные минуты
• Ваш повторный values в некоторых случаях предложения могут быть объединены в один оператор
• Это не «Расписание», это «Расписание»
• Никогда (никогда) жестко запрограммируйте идентификатор даже для тестовых данных. Выберите на основе других известных вам столбцов. Точно так же никогда не устанавливайте числовое значение по умолчанию для значения внешнего ключа.
• Добавьте больше ограничений к своим датам — например, вы знаете, что модифицированные никогда не будут созданы раньше; а день недели должен быть от 1 до 7.
• Мне приятно видеть, что в целом вы используете логические столбцы и столбцы, не допускающие значения NULL, на самом деле довольно прилично.
• Ваш createdat а также modifiedat не следуйте принятому вами соглашению о подчеркивании для имен столбцов, поэтому добавьте некоторые подчеркивания.

Предложенный

create table Services(
    id integer auto_increment primary key,
    service varchar(255) not null,
    length_in_min float not null default 20,
    capacity integer not null default 1
);


create table Schedules(
    id integer auto_increment primary key,
    day integer not null check(day between 1 and 7),
    open time not null,
    close time not null check(close > open),
    service_id integer not null references Services(id)
        on delete cascade on update cascade,
    created_at timestamp default now(),
    modified_at timestamp default now() check(modified_at >= created_at),
    starts_from datetime not null default now(),
    ends_at datetime not null default '9999-01-01 00:00:00' 
        check(ends_at >= starts_from)
);


create table Clients(
    id integer auto_increment primary key,
    name varchar(255) not null,
    email varchar(255) not null,
    phone varchar(255) not null,
    legals boolean default true
);


create table Appointments(
    id integer 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,
    created_at timestamp default now(),
    modified_at timestamp default now() check(modified_at >= created_at),
    starts datetime not null,
    ends datetime not null check(ends > starts),
    paid_online boolean not null default false,
    approved_by_client boolean not null default false
);


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 only USG apointments
    ('VISION', 10, 1); -- if VISION allow only VISION apointments
    
insert into Schedules(service_id, day, open, close) 
select id, 1, '10:00:00', '18:00:00' from Services where service="ANY";

insert into Schedules(service_id, day, open, close)
select id, 3, '08:30:00', '16:00:00' from Services where service="USG";

insert into Schedules(service_id, day, open, close)
select id, 4, '08:30:00', '16:00:00' from Services where service="VISION";

insert into Clients(name, email, phone) values ("Wiktor", "some@email.com", "000 000 000");

select sh.id as schedule_id, sh.day, sh.open, sh.close, sh.service_id, 
       sh.starts_from, sh.ends_at, se.id as service_id, se.service, 
       se.length_in_min 
from Schedules sh 
left join Services se on sh.service_id = se.id;

https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=fc5aba0479e743e9dff869c5635579fd