-1

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)

enter image description here

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?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Asagao
  • 44
  • 6
  • [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) – philipxy May 26 '22 at 18:45
  • Voting is anonymous. ERD content in text DDL. Please research before considering posting. [How to design a product table for many kinds of product where each product has many parameters](https://stackoverflow.com/q/695752/3404097) [More](https://stackoverflow.com/a/2945124/3404097) [And more](https://stackoverflow.com/q/5106335/3404097) [Re EAV](https://stackoverflow.com/a/23950836/3404097) etc etc etc [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] – philipxy May 27 '22 at 01:21

1 Answers1

1

I could help, but I don't really understand what exactly a user is supposed to do? Is he supposed to order products on the website? If so, maybe the naming of the tables should be different, eg "order_product" instead of "product"?

database diagram

and as for the performance, at this stage and with this structure there should be no problems.

user13977214
  • 46
  • 1
  • thanks for your help. I understand it is basically the design as same as I made, is it? "User" I mentioned in the question is actually "Product vendor" and this is to register products in kinda market place. I was thinking there could be better design as it was a bit complex to make all the crud apis for each tables. – Asagao Jun 20 '22 at 17:16