I developed a food ordering system, but I have doubts about whether the way I modeled the database is the best in terms of performance and longevity (how the system was to behave with a large amount of data). >
I have the following table structure:
- Orders
- Request_Items with fields: RequestID, ItemID
- Request_Item_SubItems with fields: RequestID, ItemID, SubItemID
That is, when a request is created it has items that compose it. These items are saved in the DB by receiving the order ID that it belongs to. It is possible that the items have SubItems (think of how a burger can have lettuce, tomato, onion), the SubItems receive the order ID and Item ID it belongs to.
Based on this structure and thinking long term, it would have a gigantic Items and SubItems table. When generating reports would I lose a lot of performance to bring this information? Is there a better solution?
I thought of saving the Items and SubItems in JSON in a field in the Orders table, but my host service does not provide a MySQL so up-to-date.