1
I developed a food ordering system, but I have doubts whether the way I modeled the database is the best in terms of performance and longevity (how the system was to behave with a lot of data).
I have the following table structure:
- Requests
- Pedido_items with fields: Pedidoid, Itemid
- Pedido_item_subitems with fields: Pedidoid, Itemid, Subitemid
That is, when an order is created it has items that compose it. These items are saved in the DB receiving the ID of the order it belongs to. It is possible that the items have Subitems (think about how a hamburger can have lettuce, tomato, onion), Subitems receive the order ID and the Item ID it belongs to.
Based on this structure and thinking long-term, it would have a gigantic table of Items and Subitems. At the time of generating reports I would lose a lot of performance to bring this information ? There is a better solution ?
I thought about saving the Items and Subitems in JSON in a field in the Orders table, but my host service does not provide such an updated MYSQL.
I would just make a change, which would be remove
PedidoIdtablePedido_Item_Subitems, because you already have this information in the tablePedido_items. In question the performance , if you create your query properly, you will get the maximum performance that your database allows. Since your model is correct by the information you gave us in your question.– Rodrigo K.B
Thanks for the comment, I will follow the recommended.
– Drealler