Ограничения на битемпоральную базу данных

Question

Фон:

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

Чтобы проиллюстрировать это, давайте рассмотрим простой случай с двумя таблицами:

|===============|        |==================|
|   tblOrder    |        |   tblOrderItem   |
|============== |        |==================| 
| - OrderId     |        | - OrderItemId    |
| - OrderNumber |        | - FK_OrderId     |
|===============|        | - Amount         |
                         |==================|

FK_OrderId это внешний ключ к tblOrder,

Чтобы сделать эту модель базы данных битемпоральной, я придумал следующий дизайн:

|===============|        |==================|        |====================|
|   tblOrder    |        |   tblOrderItem   |        |   tblVersions      |
|============== |        |==================|        |====================|
| - Id          |        | - Id             |        | - VersionId        |
| - OrderId     |        | - OrderItemId    |        | - VersionDate      |
| - OrderNumber |        | - FK_OrderId     |        |====================|
| - VersionId   |        | - Amount         |
| - IsDeleted   |        | - VersionId      |
| - StartDate   |        | - IsDeleted      |
| - EndDate     |        | - StartDate      |
|===============|        | - EndDate        |
                         |==================|

Пояснения:

• VersionId столбцы являются внешними ключами к tblVersions Таблица. Для каждого изменения в базе данных, запись в tblVersions таблица создана. Текущее состояние данных - это просто сумма всех версий. Это позволяет восстановить предыдущие состояния базы данных (через WHERE VersionDate < ... пункт). Это измерение времени транзакции битемпоральности.
• tblVersions table could also be avoided if we're just including the VersionDate` в две таблицы данных.
• StartDate а также EndDate столбцы - действительная временная размерность битемпоральности. Да, EndDate является излишним, мы могли бы моделировать таблицы только с StartTime,
• Id столбцы двух таблиц являются новыми первичными ключами. Поскольку у нас есть несколько строк для одного и того же объекта (несколько версий, несколько диапазонов дат в действительное время), идентификатор объекта не может быть первичным ключом таблицы. Колонны OrderId а также OrderItemId являются идентификаторами сущности, но не первичным ключом таблицы. Вместо создания новых первичных ключей Id мы также могли бы определить первичный ключ как (OrderId, VersionId, StartDate),
• Если объект удаляется, мы просто создаем новую запись версии, а также запись в таблице сущностей с IsDeleted = 1, Все остальные записи в таблице (вставки и обновления) имеют IsDeleted = 0,
• Колонка FK_OrderId из tblOrderitem ссылается на столбец OrderId из tblOrder, Это уже не настоящий внешний ключ (в смысле ограничения базы данных), так как OrderId больше не первичный ключ. Но это все еще говорит нам, какие OrderItems являются частью определенного Order.

Кажется, это работает хорошо, мы создали необходимые CRUD-запросы и можем читать и записывать битемпоральные данные.

Вопрос:

Какие ограничения мне нужны, чтобы это работало последовательно?

Меня не интересует, как реализовать ограничения (следует ли реализовывать их как ограничения базы данных, такие как FOREIGN KEY с или UNIQUE ограничения, или TRIGGER с или CHECK с, что угодно). Мне просто нужно знать, какие типы ограничений мне нужны.

Я выяснил кучу ограничений, которые я опубликую в качестве ответа. Но может быть, есть еще?

Answer 1

(Я использую аббревиатуру PIVT = "точка в действительном времени". Это обозначает определенный момент времени в действительном измерении времени)

Вот ограничения, о которых я уже подумал:

• FK_VersionId : Очевидно, нам нужны стандартные ограничения внешнего ключа на VersionId колонны.
• Битемпоральная уникальность: также сочетание (OrderId, VersionId, StartDate) должен быть уникальным (и одинаковым для tblOrderItem).
• Действительная сериализация времени: нам нужно проверить, что для каждой сущности и каждой версии не существует перекрытий в действительном времени, то есть столбцах StartDate а также EndDate не перекрываются и StartDate всегда раньше, чем EndDate,
• Целостность удаления. Нам необходимо убедиться, что для каждого объекта и каждого PIVT существует не более одной строки с IsDeleted = 1 и, если есть такая строка, после этой строки не должно быть никакой версии объекта.
• Ссылочная целостность: нам нужно проверить, что для каждого объекта OrderItem, каждой версии и каждого PIVT значение FK_OrderId устанавливается в значение, которое идентифицирует сущность Order, которая существует в данном PIVT, которая была вставлена ​​в более раннюю версию и которая не была удалена (путем установки IsDeleted = 1).