Mysql – database design help with multiple table relationships

database-designMySQL

Been scratching my head on how to set this up and looking for your help. Trying to create an API and not 100% how my tables should relate to one another.

So lets say I have users, food, and food details. The user is able to post what he wants based on the food he selects. But they all should be able to be viewed in a single view if needed. not pizza only or pasta only. Each post should be able to stream together. pizza posts + pasta posts…

Here is what i am thinking below-

  • User Table: user_id, email, pass
  • Post Table: post_id, user_id
  • Pizza Table: post_id, peperoni, sausage, peppers
  • Pasta Table: post_id, sauce, cheese, onions

Would this way make sense? But then how would i know what pizza or pasta table to look into with just a post_id… This is the part i am a scratching my head over.

Thanks in advance.

Citti

Edit:

api uri would be something like: /api/v1/posts

Example output for this would be a feed per post:

  • post_id 1 | Steve wants Pizza with Peperoni and Peppers @ 12:40
  • post_id 2 | Steve wants Pizza with Sausage and Peppers @ 12:35
  • post_id 3 | Kevin Wants Pizza with Peperoni, Sausage, and peppers @ 12:20
  • post_id 4 | Steve wants Pasta with Sauce and Cheese @ 12:15

Best Answer

Consider it this way. Products and possible options of products comprise system dictionary. Orders and specs are constraint by dictionary.

Products
--
Pizza
Pasta
;
Options
--
peperoni
sausage
peppers
sauce
cheese
onions
;
ProductOptions
--
Pizza peperoni
Pizza sausage
Pasta cheese
Pasta onions
;
Orders
--
1 Steve Pizza 12:40
2 Steve Pizza 12:35
;
OrderSpecs
--
1 Peperoni
1 Peppers
2 Sausage 
2 Peppers
;
Related Question