Обеспечение целостности данных супертипа и подтипа без хранимых процедур, триггеров или пользовательских функций

Question

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

В качестве первого шага я предложил логическую модель, приведенную ниже. Мои знания по моделированию данных ограничены, поэтому в моей диаграмме могут быть ошибки, но, надеюсь, это передает мое намерение. Обратите внимание, что это просто упрощенная часть более крупной схемы, и для простоты я представляю только соответствующие таблицы.

Модель данных вкратце

• У клиента Ordr может быть один или несколько OrdrItems
• OrdrItem может быть или FoodItem или ComboItem
• ComboItem - это логическая группа из двух или более FoodItems.

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

Например, FoodItem является подтипом OrdrItem. Для каждой строки в FoodItem должна быть ровно одна соответствующая строка в OrdrItem. Однако в его текущей реализации я могу удалить строку из FoodItem, оставив строку в OrdrItem без соответствующей строки в одной из таблиц подтипа. Это должно быть запрещено.

Некоторые дополнительные ограничения данных

• У заказа должен быть хотя бы один связанный "элемент заказа" (т. Е. Заказ не может быть пустым)
• Элемент заказа должен иметь ровно один связанный подтип FoodItem или ComboItem (т. Е. OrderItem не может быть одновременно FoodItem и ComboItem).
• Некоторые дальнейшие ограничения, вероятно, возникнут в будущем

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

Вопрос

У меня есть только самые слабые знания о хранимых процедурах, триггерах и пользовательских функциях. У меня складывается впечатление, что некоторые или все эти функции могут помочь мне достичь того, чего я хочу. Однако, если я смогу выполнить работу только с проверочными ограничениями, внешними ключами и относительно простыми функциями, я с удовольствием пойду по этому пути. По сути, я хочу максимально ограничить сложность и не использовать каждую изящную функцию базы данных, если она не гарантирована. Можно ли обеспечить требуемую целостность данных, не прибегая к хранимым процедурам, триггерам, пользовательским функциям и другим более эзотерическим функциям базы данных?

Я готов использовать MySQL или Postgresql для реализации своего решения, так как у меня есть базовые знания обеих систем.

Наконец, если такой подход к целостности данных считается излишним, или если существует гораздо более прагматичное, но немного несовершенное решение, я также открыт для этого.

Answer 1

К сожалению, "современные" СУБД не поддерживают напрямую все причудливые символы¹, которые можно вставить в диаграмму ER. Все, что на самом деле обеспечивает физический FOREIGN KEY, это то, что дочерняя строка не может существовать без родителя, что дает вам простое отношение "от 1 до 0 или N" ².

Вы можете...

• сделать FK NULL-способным преобразовать левую сторону отношения в "0 или 1"
• и / или вы можете положить ключ сверху FK, чтобы изменить правую сторону на "0 или 1"

... но это все, что вы можете сделать "из коробки".

Чтобы применить другие правила³, вам придется либо значительно "унизить" модель и, возможно, использовать отложенные ограничения⁴, либо вы можете сделать это в процедурном коде⁵.

В то время как ваши инстинкты о том, что в самой базе данных есть как можно больше правил целостности, все же считаются меньшим злом, просто приводить в исполнение "необычные" случаи в процедурном коде вместо того, чтобы скручивать модель данных в крендель для размещения ограничения декларативных ограничений.

Фактически, одним из самых популярных методов является создание "API":

• запретить клиентам изменять таблицы напрямую (отменив соответствующие разрешения)
• и разрешить им изменять данные только с помощью хранимых процедур, которые вы написали и которые обеспечивают соблюдение всех необходимых бизнес-правил⁶. Таким образом, вы направляете всех клиентов через один и тот же "расчетный центр", и никто не может плохо себя вести.

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

---

¹ Например, наследование (он же подтип, категория, иерархия обобщения).

² Левая сторона: у каждого данного ребенка должен быть "1" родитель.Правая сторона: у любого данного родителя должно быть "0 или N" детей.

³ Такие, как эксклюзивность и присутствие детей, что важно для наследования, как вы уже отметили.

^4, которые поддерживаются PostgreSQL, но не MySQL.

⁵ В порядке предпочтения:

• триггеры и хранимые процедуры
• средний уровень
• или клиент.

⁶ Но будьте осторожны с условиями гонки: изоляция транзакции защитит вас от некоторых из них, но не от всех, и вам может потребоваться сделать явную блокировку.