Optimal Database design for 3-way relationship

database-designrelational-database

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.

Best Answer

Three points:

First, you need to use JOIN to good advantage, so as to retrieve the joined data that you want with one trip to the database, instead of repetitive trips to the database. As James Anderson correctly pointed out, this is what relational databases are all about.

However, if you are just learning how to use a database in SQL, then using a database of your own design may take you down the wrong trail. The database needs to be designed well to exploit the full power of JOIN, and of two other relational operators, known as restrict and project in relational math. The SQL buzzwords are WHERE, DISTINCT, and GROUP BY.

Second, the relationship between packages and products is your basic bill-of-materials case. This case has been extensively studied in practice over the last forty years. There's no need for you to reinvent the wheel. However, if you add the possibility that packages can be composed of other packages, then the BOM problem becomes a recursive one. SQL is not built for recursion, but there are workarounds.

If you come up to speed on the nested-set technique for designing containment hierarchies, like BOM, then you reduce what would have been a recursive query into a simple SQL query that the database server can decompose into a simple reiterative process for you. Nested-set is not ultimately complicated, but there is a learning curve here.

Third, the relationship between order details and products or packages is not really a ternary relationship, in the way that term is usually used. Each order detail references an order (the parent), and either a product or a package. This either-or situation is your typical class-subclass (type-subtype) situation, rather than a true ternary relationship. Unfortunately, vanilla SQL is not good at inheritance. You could use extensions to SQL peculiar to your DBMS, if it has them, or you could use one of two techniques, called "single table inheritance" or "class table inheritance". I like Fowler's treatment of this subject.

There's plenty to learn here. You sound like a smart programmer, so you should be able to learn it fast. Just don't assume you already know it. Maybe you do, maybe you don't.

Related Topic