sql - 如何构建 SQL 数据模型以维护数据完整性

Question

背景

我正在建立私有(private)厨师预订服务，您可以在其中预订厨师为您烹制自定义菜单。我在创建准确表示域同时保持数据完整性的 SQL 数据库架构时遇到问题。

要求

客户创建 Booking通过选择 Chef和 Menu并选择 MenuItems他们希望为每个 MenuCourse

一个 Chef定义了一组 MenuItem客户可以从中选择创建他们的Booking

一个 Menu是 MenuCourses 的集合. (例如，名为“品尝菜单”的 Menu 是 6 道菜，每道菜 MenuCourse 的价格在 10-20 美元之间)。

一个 Chef应该能够关联他们的MenuItems有多个 Menus和 MenuCourses .

一位客户Booking应该包含 Chef与 Menu 一起选择的客户(以及 MenuItems )将被提供。 Booking价格由 Menu 决定和 MenuCourse精选(开胃菜价格低于主菜)

问题

在我当前的数据模型中，我有以下不知道如何解决的问题:

A. 可以创建 Booking与 Chef “A”，但随后有 BookingMenuItem引用 MenuItem与 Chef “B”( BookingMenuItem 的所有 Booking 应该属于同一个 Chef )

B. A Booking引用特定的 Menu (我需要定价，定价基于 Menu 和 MenuCourse )但是 BookingMenuItem对于那个 Booking可以引用完全不同的 Menu或 MenuCourse
是否可以重新设计我的数据库架构来解决我遇到的完整性问题？还是我只需要在应用程序级别实现这些检查。谢谢!

Answer 1

这使用模型中的表名并尽可能接近它。我改名为 menu_item至dish , 以免混淆。

customer {CST}
     KEY {CST}

chef {CHF}
 KEY {CHF}

dish {CHF, DSH_NO}
 KEY {CHF, DSH_NO}

FK {CHF} REFERENCES chef {CHF}

menu {CHF, MNU_NO}
 KEY {CHF, MNU_NO}

FK {CHF} REFERENCES chef {CHF}

menu_course {CHF, MNU_NO, CRS_NO}
        KEY {CHF, MNU_NO, CRS_NO}

  FK {CHF, MNU_NO} REFERENCES
menu {CHF, MNU_NO}

menu_course_item { CHF
                 , MNU_NO
                 , CRS_NO
                 , CRS_ITM_NO
                 , DSH_NO
                 }
KEY {CHF, MNU_NO, CRS_NO, CRS_ITM_NO}

        FK1 {CHF, MNU_NO, CRS_NO} REFERENCES
menu_course {CHF, MNU_NO, CRS_NO}

 FK2 {CHF, DSH_NO} REFERENCES
dish {CHF, DSH_NO}

booking { CST
        , BOOK_NO
        , CHF
        , MNU_NO
        }
    KEY {CST, BOOK_NO}
     SK {CST, BOOK_NO, CHF, MNU_NO}

FK1 {CST} REFERENCES customer {CST}

 FK2 {CHF, MNU_NO} REFERENCES
menu {CHF, MNU_NO}

booking_menu_item { CST
                  , BOOK_NO
                  , CHF
                  , MNU_NO
                  , CRS_NO
                  , CRS_ITM_NO
                  }

KEY {CST, BOOK_NO, CHF, MNU_NO, CRS_NO, CRS_ITM_NO}

    FK1 {CST, BOOK_NO, CHF, MNU_NO} REFERENCES
booking {CST, BOOK_NO, CHF, MNU_NO}

             FK2 {CHF, MNU_NO, CRS_NO, CRS_ITM_NO} REFERENCES
menu_course_item {CHF, MNU_NO, CRS_NO, CRS_ITM_NO}

我使用了通用术语 KEY对于 PK or AK .如果出于某种原因，您需要使用单列技术 key ( xx_ID)，请确保添加它们，如 this example 中所述;如果没有，所有这些 KEY是 PK .

笔记:

All attributes (columns) NOT NULL

KEY = PK or AK

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key