I am trying to draw the table diagram of a product listing web application.
Application logic:
Admin user will create:
- Categories (must be dynamically created by admin user),
- Category's attributes (must be dynamically created by admin user),
- Selection of Category attribute's options (must be dynamically created by admin user)
User will post a product under a specific category and must choose category attribute's option for each category attributes to successfully save the product.
Example: Admin creates category "food"
- "food" has its attribute "meal_type" and "sugar".
- "meal_type" has its options "breakfast", "lunch" and "dinner".
- "sugar" has its options "with-sugar" and "non-sugar".
and now user can post his product "pizza" under category "food". When user chosen "food" category, he must see its attributes "meal_type" and "suger" and then must set those option values.
For example, "pizza"'s category is "food" and "meal_type"="breakfast", "sugar"="with-sugar".
And I want to call these option values together with product details when selecting these items.
I have made this (removed non-related fields)
but I am not sure this will work and be efficient at same time.
What is the most efficient relational schema (or ERD) for case like this?
