Таблица структурирования неисправностей для уникального индекса

Question

У меня проблемы с поиском решения проблемы структурирования таблицы для уникального индекса.

У меня есть таблица, где элементы зарезервированы, и есть несколько полей, которые используются для

ItemID - INT
Date - DATE
TimeOfDay - INT (morning = 1, afternoon = 2)
ReservationStatus - VARCHAR (expired, cancelled, confirmed, cancelled by admin)

Проблема с полем ReservationStatus. Система должна учитывать несколько строк, которые были отменены, но только одна подтвержденная или просроченная (приложение изменяется с подтвержденной на просроченную). У меня нет идей, любая помощь будет оценена.

Редактировать:

Полная структура таблицы

ReservationID - PK Auto-incrementing Integer
SubItemID - INT FK
MemberID - INT FK
Date - DATE
TimeOfDay - INT
ReservationStatus - VARCHAR

SubItemID, Date, TimeOfDay, ReservationStatus должны быть уникальными: несколько участников не могут зарезервировать один и тот же SubItem во второй половине дня в одну и ту же дату.

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

Answer 1

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

Вы можете добавить столбец с именем CancellationCode. Сделать это INT. Зарезервируйте значение 0 в этом столбце, чтобы означать активный / просроченный, и установите это значение по умолчанию. Затем, если вы отмените резервирование, назначьте уникальное ненулевое значение для CancellationCode в этой строке. Для этого вы можете использовать значение ReservationID в строке или таблицу отмены с собственным уникальным идентификатором.

Вы можете оставить свой столбец ReservationStatus, чтобы узнать, что происходит с резервированием. Но когда вы просматриваете активные / просроченные бронирования, используйте WHERE CancellationCode = 0, на всякий случай отмены синхронизации CancellationCode и ReservationStatus.

Затем создайте уникальный индекс (SubItemId, Date, TimeOfDay, CancellationNumber). Это будет препятствовать любым попыткам вставить новые активные строки резервирования для тех же комбинаций значений SubItemId, Date, TimeOfDay, что и существующие строки.