База данных по записи — дизайн II

Я хочу создать базу данных, которая дает пользователю-администратору возможность создавать расписание для других пользователей для бронирования. Это вторая редакция этой схемы базы данных.

Я хотел бы знать, есть ли какие-либо структурные / логические проблемы с этой схемой. Что бы вы изменили и почему, или если вы видите какие-либо потенциальные проблемы относительно его первоначального назначения.

Что бы вы использовали для хранения цены? Я знаю, что FLOAT и DOUBLE не являются точными типами данных (оценка), поэтому мы не должны использовать их для хранения цены. Таким образом, мы можем выбрать DECIMAL или INT.

Decimal кажется хорошим выбором, однако я видел некоторые платежные шлюзы, которые заставляли использовать INT для хранения своих значений перевода (поэтому, если цена была 11,99, вы отправляете их 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
);

• Ваш -- TABLE Services комментарий является избыточным и может быть удален вместе с другими подобными комментариями
• Ваш псевдо-встроенный синтаксис, используемый для обозначения ваших ограничений … в порядке, я думаю? Обычно я не придаю особого значения ограничениям именования, но это один из способов сделать это.
• Раньше у вас было on delete а также on cascade статьи везде. Это был правильный поступок, и упустить их несколько рискованно. В MariaDB по умолчанию используется не каскад, как вы использовали, а скорее ограничивать. Это разумное значение по умолчанию (более разумный IMO, чем Postgres, чье значение по умолчанию — «без действий»), но это нет эквивалентно тому, что вы использовали во внешних ключах вашего первого вопроса. В сторону: PostgreSQL помещает много усилий, чтобы соответствовать стандартам, поэтому я ожидал, что его реализация будет более стандартной, но я не проверял это.
• Написание «расписания» не изменилось за последние 48 часов.
• Как и в предыдущем вопросе, вы все равно не должны жестко кодировать идентификаторы. Не указывайте жестко 1 для идентификатора вашего персонала и не передавайте любые другие числовые значения для идентификаторов в виде литералов, подобных этому. Это гарантированно нарушит идемпотентность и валидность вашего DML в различных типичных ситуациях. Это выходит за рамки соблюдения стандартов и передовых практик и становится откровенно неверным, а решения достаточно тривиальны: либо выберите на основе других столбцов, которые вы знаете, например, фамилии сотрудников, либо более надежно используйте returning пункт в вашем insert просто дать вам нужный идентификатор и поместить его в переменную.
• Мне приятно видеть, что вы использовали десятичную дробь для денежного выражения.

ОБНОВЛЕНИЕ схемы на основе предложений Райндериена

• написание «расписание»
• рекомендации о том, как правильно ВСТАВИТЬ ID внешнего ключа в таблицу

+ небольшие настройки, добавляющие UNIQUE в некоторые таблицы (Clients.email и Services.name), чтобы использовать эти столбцы для выбора уникального идентификатора.

Посмотреть общую визуализацию [here]

Пс. Мне все еще нужно id INT AUTO_INCREMENT PRIMARY KEY если я могу использовать его email как получить уникальную строку данных?

База данных теперь выглядит пустой:

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;

Зарезервированные ключевые слова

Будьте осторожны с излишне общими ключевыми словами, есть большая вероятность, что они зарезервированы. Имя, Открыть, Закрыть являются зарезервированные ключевые слова в MySQL.

Соответственно, я предлагаю вам переименовать некоторые объекты из «name», например, в: location_name, staff_name.

VARCHAR

VARCHAR (255) звучит как значение по умолчанию, предлагаемое инструментом дизайна. Для адреса электронной почты необходимость в 255 символов сомнительна. Для телефонного номера это определенно слишком много. Я не думаю, что даже в международном формате он будет превышать 20 символов.

Подумайте о пользовательском интерфейсе и о том, какие ценности вы готовы принять, даже при сохранении гибкости. Затем смоделируйте свою базу данных в соответствии с разумными ожиданиями из реального мира.

Этот выбор «дизайна» может (а может и не иметь) повлиять на производительность в зависимости от ряда факторов. Обсуждение: Есть ли недостатки в использовании универсальной переменной varchar (255) для всех текстовых полей? а также MySQL — длина и производительность varchar.

Дело

Советую использовать полную нижний регистр для всех имен объектов. Имейте в виду, что в системах Linux файловая система обычно с учетом регистра и это относится к таким объектам, как таблицы.

Индексы

А как насчет индексов? Если ваши таблицы продолжают расти, запросы будут выполняться все дольше и дольше, потому что они никоим образом не оптимизированы. Подумайте о СОЕДИНЕНИЯХ и о том, в каких полях вы собираетесь выполнять поиск.

УНИКАЛЬНЫЙ

У вас есть УНИКАЛЬНОЕ ограничение на адрес электронной почты, что имеет смысл. Однако в реальном мире вы можете оказаться в ситуации, когда два человека (например, супруги) используют один и тот же адрес электронной почты. Так это хорошая идея? Это может быть крайний случай, но я бы ослабил требование. Дело в том, что адрес может быть критерием поиска, но не единственным идентификатором клиента.

Разное.

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

Дата и время

В таблице Staff вы определили поле dob как:

 dob YEAR NOT NULL

Это не соответствует вашему примеру вставки (‘1988-01-01’). Вы можете просто использовать ДАТА тип данных.

Что касается выбора типов данных: table Appointments использует TIMESTAMP и DATETIME, но они не эквивалентны. Чтобы узнать подробности, перейдите по ссылке выше. Имейте в виду, что TIMESTAMP имеет 2К38 проблема:

TIMESTAMP имеет диапазон от ‘1970-01-01 00:00:01’ UTC до ‘2038-01-19 03:14:07’ UTC.

Так что любая программа, которая полагается на это, не годится для будущего. Я рекомендую вам использовать datetime и для автоматического обновления последнего измененного времени: Автоматическая инициализация и обновление для TIMESTAMP и DATETIME

Какое обоснование:

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

в таблице расписания? Просто оставьте его по умолчанию равным нулю, тогда ваша программа будет знать, что событие не завершено.

И последнее, но не менее важное: предполагая, что вы находитесь в США, и принимая во внимание возможность того, что ваша программа может быть развернута в нескольких местах по всей стране (даже в отдаленном будущем), вы можете рассмотреть возможность использования данные с учетом часовых поясов. Хотя это добавляет начальную сложность, это также обеспечивает гибкость и адаптируемость. В конце концов, datetime без контекста бессмысленна.

Форматирование чисел

В той же таблице вы сохраняете номер телефона, например: «888 888 888». Рекомендация: храните числа без пробелов и искусственных символов. Позвольте вашей программе обрабатывать форматирование чисел. Я бы, возможно, добавил код страны, если у вас есть зарубежные клиенты. Или вы можете сохранить все номера в международном формате, например: +18004567890.

В таблице Расположение:

post VARCHAR(6) NOT NULL

Это почтовый индекс? Тогда возможен вариант беззнакового int с нулевым заполнением. Но это годится только для адресов в США. Для британского почтового индекса это было бы немного короче.

Кодировка

Какую кодировку вы используете? Будет ли ваше приложение плавно обрабатывать символы с диакритическими знаками? Сохранится ли клиент Хосе Пеньоса в базу данных без сбоев?