I am designing a database with stores, product, product_categories, product_options and of course I want to implement the ordering system.
To explain further:
- A store has some products
- A product can have some categories (e.g if we are talking about food then those categories would be: size, toppings, ingredients)
- A product category will contain the category's options( e.g if we talking about a product with category size then the options would be: small, medium, large)
Database EER diagram
The question is how to implement the order table. For example lets say we have the following order:
{
"product": {
"id":1,
"categories":{
"category1": {
"id":1,
"name":"size",
"options":{
"option1":{
"id":3,
"name":"large",
"price":"5.4"
}
}
},
"category2": {
"id":1,
"name":"ingredients",
"options":{
"option1":{
"id":2,
"name":"onions",
"price":"0.8"
},
"option2":{
"id":3,
"name":"tomato",
"price":"0.5"
}
}
}
}
}
}
Best solution so far using mysql, is to create the corresponding order_product, order_product_categories, order_product_options and the main orders table in order to keep up with the the volume of information that an order contains.
