1

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:

  • 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)
  • 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

Improve this question
8
  • The first thing to note about this is that it's possible for someone who doesn't work in a restaurant, to design a menu system that is far more complex (and possibly still more restrictive) than a customer will tolerate. Secondly, I think what you're dealing with here is essentially a "bill of materials" problem. Commented Apr 21, 2020 at 19:53
  • @Steve thanks, I think BOM could be a good fit for composing the Menu out of courses + items, I'm not quite sure how to model that though since I'd wanna make sure a MenuItem isn't a parent of a MenuCourse Commented Apr 23, 2020 at 17:23
  • A typical approach is to have a recursive structure, and incorporate some distinction between basic parts (which cannot be linked to other parts), and assemblies of such parts (which can be linked either to parts, or to other assemblies). Commented Apr 23, 2020 at 17:34
  • @Steve do you have an example you could point me to? Does a recursive BOM model typically use STI to model each type of "part"? thanks! Commented Apr 23, 2020 at 23:31
  • I don't have a coded example offhand, but it's really not complicated. A basic approach applied to your case is to have a table with names, types, and prices for all menu elements (e.g. all individual food items, plus courses and set menus), and a second table which expresses the structural links of those elements (i.e. at minimum, a parent id column and a child id column). You need two tables because the elements will I assume be many-to-many (courses can contain multiple different food items, and any given food item could be found in multiple defined courses/set menus). Commented Apr 23, 2020 at 23:59

1 Answer 1

Reset to default
1

Having the same Chef for MenuItems and related BookingMenuItems might be a good default setting, but nothing which should be enforced through the database. In reality, it could be perfectly possible that Chef A has defined a menu with it's items, but then he/she is not available and a different Chef B has to take over for the cooking. So in short: let the application manage this, expect exceptions.

For your second issue, a technical solution could be implemented through the application, or through triggers at the the database level. It is not always possible or sensible to remove every kind of cycle in a relational DB scheme.

However, I would recommend to reconsider the design first:

  • expect a customer to have "extra wishes" for items which are not offered by default on the menu - so it should be possible to book extra items (and those should have a price as well), or existing items with some special requirements for their preparation, and maybe a price modification ("I want to have my salad without tomatoes, but with an extra portion of cottage cheese" - Ok, that's fine, we can prepare this for you for only 3 bucks 49 cents).

  • expect that not all the menu items offered at the time of the booking might be available at the time when the Chef wants to cook it, and they have to find a replacement (maybe with some reduced pricing)

  • bookings have a date, and menu base prices as well as planned menu items for a certain menu can change over time, but the price at the time when the booking was placed must be usually fixed for the customer. So when a booking is placed, I would suggest to make a copy of the menu with its current price (maybe into a BookedMenu table), copy the menu items belong to this booked menu and allow all kind of individual changes to be applied to this copy.

So better model these things separately:

  • the available items (independently from the menu, the chef and the current pricing)

  • the offered menus and items, with the offered prices

  • the booked menus / items with the price at the time of the booking, and extra customer wishes

  • the items at the time when the meal is preparated and served, maybe with the changes due to supply shortages etc

After you got this model complete, you should recheck which redundancies/potential inconsistencies exist. Currently, the second issue seems to exist because the model does not clearly differentiate between the different states in time, the menu as a "template" for a booking, the menu how it was booked and the menu how it was cooked and served.

Improve this answer
1
  • thanks for the feedback, I simplified my model for this question, I do have different item "states" using join tables to represent booked items and available items....I guess I'm still running into the issue of ensuring a "booked" Menu only contains items for the booked Chef, although I guess that redundancy is ok here and can be enforced at app level Commented Apr 23, 2020 at 17:25

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.