2

Background

I'm building a Private Chef booking service where you can book a Chef to cook you a custom Menu. I'm having trouble creating a SQL db schema that accurately represents the domain while maintaining data integrity.

Requirements

  • Customers create a Booking by selecting a Chef and a Menu and choose the MenuItems they want for each MenuCourse
  • A Chef defines a set of MenuItem that a customer can choose from to create their Booking
  • A Menu is a collection of MenuCourses. (ex. A Menu named "Tasting Menu" is a 6 course meal, where each MenuCourse costs between $10-20).
  • A Chef should be able to associate their MenuItems with multiple Menus and MenuCourses.
  • A customer Booking should contain the Chef the customer selected along with the Menu (and the MenuItems) that will be served. Booking price is determined by the Menu and MenuCourse selections (an appetizer costs less than an entree)

Problem

In my current data model, I have the following issues that I'm not sure how to fix:

A. It's possible to create a Booking with Chef "A", but then have a BookingMenuItem that references a MenuItem with Chef "B" (all the BookingMenuItem for a Booking should belong to the same Chef)

B. A Booking references a particular Menu (which I need for pricing, pricing is based on Menu and MenuCourse) however a BookingMenuItem for that Booking could reference a completely different Menu or MenuCourse

Is it possible to re-design my db schema to fix the integrity problems I'm having? Or do I just have to implement these checks at the application level. Thanks!

ERD

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
narciero
  • 605
  • 6
  • 17
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. Give just what you need & relate it to your problem. – philipxy Apr 24 '20 at 03:38
  • You are saying that once someone books cheesecake with Nigella Lawson, no one can ever book cheescake with anyone other chef? – Nick.Mc Apr 24 '20 at 11:44
  • @Nick.McDermaid no, each chef defines their own set of menu items, and customer can book whatever they want based on which chef they pick as part of their booking....a chef can be booked multiple times by different customers – narciero Apr 24 '20 at 20:10
  • With regards to issue A, it seems like you should remove `chef_id` from `MenuItem`. You can work out what menuitems a particular chef is booked for and vice versa by traversing the joins through to `Booking` With regards to issue B, is this because patrons might book a set menu or they might book a custom menu from individual items? – Nick.Mc Apr 25 '20 at 04:23

1 Answers1

2

This uses table names from your model and follows it as close as I could get it. I renamed menu_item to dish, to avoid confusion.

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}

I have used generic term KEY for PK or AK. If, for some reason, you need to use single-column technical keys (xx_ID), make sure you add them, as described in this example; if not, all these KEY are PK.

Note:

All attributes (columns) NOT NULL

KEY = PK or AK

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key
Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71