-1

PB&J are a stand in for the real data I want to keep a secret. I want to catalog peanut butter & jelly sandwiches (PB&J) offered by different restaurants to determine which restaurants offer the sandwich which most matches a user's taste. The constraints:

  1. PB&J sandwich has components:
    • BREAD (white, wheat, brioche, etc.).
    • JELLY (strawberry, grape, etc.).
    • PEANUT BUTTER (creamy, crunchy, etc.).
    • OTHER INGREDIENTS (bananas, chia seeds, honey, marshmallow fluff, etc.).
    • CUT (square, triangle, etc.).
  2. You may only select one of the following components per sandwich (BREAD, JELLY, PEANUT BUTTER, CUT).
  3. You may select one or more of the OTHER INGREDIENTS.
  4. A Restaurant may have one or more PB&J's assigned to it.
  5. A User will have one PB&J assigned to them (denoting their preference). I do not like this approach because of the pbj_sandwiches table relying on matching id's with the tables they belong to in separate columns:

users:

  • id - int PK
  • name - string
  • email - string

restaurant:

  • id - int PK
  • name - string

user_sandwiches:

  • id - int PK
  • user_id - int FK UNIQUE > user.id
  • pbj_sandwich_id - int FK >- pbj_sandwiches.id

restaurant_sandwiches:

  • id - int PK
  • restaurant_id - int FK > restaurant.id
  • pbj_sandwich_id - int FK >- pbj_sandwiches.id

pbj_sandwiches:

  • id

pbj_sandwich_components:

  • id
  • pbj_sandwich_id - int FK >- pbj_sandwiches.id
  • component_id int FK (pbj_sandwich_bread.id, pbj_sandwich_jellies.id, pbj_sandwich_peanut_butters.id, pbj_sandwich_other_ingredients.id, pbj_sandwich_cuts.id)
  • component_table - string (pbj_sandwich_bread, pbj_sandwich_jellies, pbj_sandwich_peanut_butters, pbj_sandwich_other_ingredients, pbj_sandwich_cuts)

pbj_sandwich_bread:

  • id
  • name

pbj_sandwich_jellies:

  • id
  • name

pbj_sandwich_peanut_butters:

  • id
  • name

pbj_sandwich_other_ingredients:

  • id
  • name

pbj_sandwich_cuts:

  • id
  • name

I could treat the components like a taxonomy maybe? Should the component types be stored more statically in an ENUM in code instead of database (but what about when a new option needs to be added)? I need to enforce a single selection of some components, but allow multiple selections of others. The schema provided I am not happy with.

user4157124
  • 2,809
  • 13
  • 27
  • 42
mchljams
  • 441
  • 2
  • 9
  • A foreign key can only reference one table. So `component_id` that references 5 is not valid. You should combine all those tables into a single table, with an additional column that indicates the type of component it is. – Barmar Jun 20 '23 at 18:42
  • 1
    General design questions like this are not a good fit for SO's question/answer format. – Barmar Jun 20 '23 at 18:43
  • @Barmar - It's a polymorphic relationship, and not a great design, I agree. Thats why I am posting. So if all of the other tables are combined in one, what is the suggestion for enforcing one selection on some types, and allowing multiple selections on others? – mchljams Jun 20 '23 at 18:47
  • 2
    That shouldn't be enforced in the DB, it should be in application logic. – Barmar Jun 20 '23 at 18:50
  • 1
    You could add another column like `allow_multiple`, the application can check this. – Barmar Jun 20 '23 at 18:51
  • This is probably heresy, but depending on the size of your tables and types of queries you'd want to do, you could also consider some JSON fields. These days you can create virtual columns and indexes on expressions so you get quite far. – Evert Jun 20 '23 at 20:23
  • 2
    @Evert, I would not recommend using JSON to someone who is at this level of data modeling experience. Designing database models in JSON has its place, but it's _harder_ than designing normalized relational database models. – Bill Karwin Jun 20 '23 at 20:37

1 Answers1

1

If a given sandwich may have only one value for certain attributes, then make them attributes in the pbj_sandwiches table.

pbj_sandwiches:

    id
    bread_id - int FK >- pbj_sandwich_bread.id
    jelly_id - int FK >- pbj_sandwich_jellies.id
    peanut_butter_id - int FK >- pbj_sandwich_peanut_butters.id
    cut_id - int FK >- pbj_sandwich_cuts.id

This has multiple advantages. You can make real foreign key constraints, because you're no longer using polymorphic relationships.

Also you don't risk having anomalies like multiple rows in your pbj_sandwich_components table referencing the same sandwich with incompatible options (e.g. two different cut styles for the same sandwich).

For multi-valued attributes, like your additional optional components, these need to be in their own child table.

pbj_sandwich_options:

    id
    option_id - int FK >- pbj_sandwich_other_ingredients

You need one additional table for each multi-valued attribute, otherwise you get messed up with violating 4th Normal Form. See for example my answer to How to model a database with many m:n relations on a table

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828