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.
- Customers create a
Bookingby selecting a
Menuand choose the
MenuItemsthey want for each
Chefdefines a set of
MenuItemthat a customer can choose from to create their
Menuis a collection of
MenuCourses. (ex. A
Menunamed "Tasting Menu" is a 6 course meal, where each
MenuCoursecosts between $10-20).
Chefshould be able to associate their
- A customer
Bookingshould contain the
Chefthe customer selected along with the
MenuItems) that will be served.
Bookingprice is determined by the
MenuCourseselections (an appetizer costs less than an entree)
In my current data model, I have the following issues that I'm not sure how to fix:
- it's possible to create a
Chef"A", but then have a
BookingMenuItemthat references a
Chef"B" (all the
Bookingshould belong to the same
Bookingreferences a particular
Menu(which I need for pricing, pricing is based on
MenuCourse) however a
Bookingcould reference a completely different
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!