All right, I know this sounds complicated, but all relationships are, lemme clarify. Well, I have three entities in my database which are tied to each other in a string of relationships, the app is for restaurants, and the tables are as follows:
Customers Id Name Email
Packages Id Name ImageUrl Price Description Products (One package may have 1 to many products)
Products Id Name ImageUrl Price Description
Orders Id CustomerId Address
The one extra table mentioned is just for clarification, as customer can place an order. Here goes, now the relationships are defined as follows:
- Customer can place multiple orders, One order is only associated with one customer, one-to-many(this one is for clarification)
- One package can contain multiple products, one product can belong to multiple packages(consider packages as deals in a restaurant, like deals have the same items/products that the restaurant already offers), many-to-many
- One order can contain multiple products, One product may have multiple orders, many-to-many
- One order can contain multiple packages, One package can have multiple orders, many-to-many
The last three are the main we need to focus on, now the thing is how to design this in the database. Like for many-to-many we shall use referencing or relationship tables, like for e.g. in the case of packages and products a table could be created package_products and that is all fine coz we are ready to take that query cost to the new table in exchange for good methodology. And the same needs to be done for the orders and products table, a new table order_products catering to their many-to-many needs. Also, the same for the two entities, orders and packages, that is all fine. But now on the query side, we have to optimize this, like the solutions already exist, the one I mentioned, u may have something similar, what I want is something better.
If I want to for e.g. get the details of an order, which can have many packages and products at the same time, will have to query orders table, then the reference table for the orders and products to get single products, then will have to separately query the reference table for orders and packages to get the packages in it. Now that said, and fine at the moment, but if you have noticed by now, packages are made up of products, so basically orders are storing the info for a bunch of products some of whom were part of a deal/package and the other, well not. As we open up the packages in an order we basically wind up at the same product table, a kind of circle back motion. Questions:
- Is this technique bad even ? I began to think so
- How do I avoid this if it is ?
- How to eventually avoid the extra query cost as well, on the order package table and so on ?
- Is the technique which puts an extra package ID in order_products table bad, if not what would that ternary relationship be called and is that even permissible ?
Thank you in advance for reading and answering with the great virtue of patience.