Is it OK to have a many-to-many relationship where one of the tables involved is already a junction table?

Question

When planning our database, we ended up with a setup like this:

We have Company, Product and Person.

There is a many-to-many relationship between Company and Product, through a junction table Company_Product, because a given company may produce more than one product (such as "car" and "bicycle"), but also a given product, such as "car", can be produced by multiple companies. In the junction table Company_Product there is an extra field "price" which is the price in which the given company sells the given product.

There is another many-to-many relationship between Company_Product and Person, through a junction table Company_Product_Person. Yes, it is a many-to-many relationship involving one entity that is already a junction table. This is because a Person can own multiple products, such as a car from company1 and a bicycle from company2, and in turn the same company_product can be owned by more than one person, since for example both person1 and person2 could have bought a car from company1. In the junction table Company_Product_Person there is an extra field "thoughts" which contains the thoughts of the person at the moment they purchased the company_product.

Is this commonly-acceptable or is this a sign that something is wrong? If it's weird, what would be a better solution?

I've been running into a few problems with this (such as this one), so I was wondering, perhaps my database setup is wrong to begin with.

Answer 1

In making the logical design of your database, you should not think in terms of "junction tables", but in terms of relations.

Your first "junction table" seems to correspond to the relation "Company sells Product for Price". Your second "junction table" seems to correspond to the relation "Person owns Product manufactured by Company".

While it would depend on the full context, I'd likely model the second relation as a table with foreign keys to the Company, Product, and Person tables and with no connection to the other "junction table". This raises the risk that I could claim that a person owns a product that was never manufactured by a company, but it also allows deleting a row from the "sells product" table without losing all the ownership information for anybody that happened to own that product from that company. Maybe this isn't an issue for you, in which case this isn't a reason not to structure the tables in the way you suggest, and the risk I mentioned before is a reason to structure the tables in the way you suggest.

More generally, what I would recommend (besides thinking relationally rather than in terms of "tables" and "junction tables") is to consider what happens as things change. For example, let's say you want to calculate how much a person has spent on various products. If the prices can change, then you can't do this with your suggested representation (nor my suggestion without adding additional information), since the person may have bought the product at a time when the price was different. Temporal database techniques can handle these situations generally, but fully doing this is often overkill. Nevertheless, I recommend looking at temporal databases as they tend to more accurately model the situation. From there you can consider what data (if any) is unimportant and thus you can lose. (Temporal databases are usually "insert-only".) I suspect if you factor in how the data can change over time, you will want a schema different from your original suggestion and my suggestion.

Answer 2

Suppose that we have a junction between 2 tables (AB), and then between them and yet another table (ABC).

We can say that AB may be a much smaller relation, so it can be kept in RAM, while ABC is huge and won't fit. So queries touching AB would be faster that queries to ABC. A separate index on just A and B fields on ABC would still be much larger than an index on unique AB pairs.

OTOH I would not make ABC depend on AB for a FK; instead I'd make it depend on A, B, and C directly.

Answer 3

OP here.

After reading the provided answers and further researching and thinking about the subject, I concluded that there's nothing technically wrong with my setup per se. However, since I was facing a practical problem with it, I found a solution that consisted in reworking a bit the relationships in my scheme. I don't know if my difficulties were specific to the JavaScript ORM I was using (namely sequelize) or would exist in other ORMs as well, but regardless, I found a different setup that also makes sense and avoids this problem.

Thus, I decided to post my own answer here too, so that if anyone stumbles in a similar situation, perhaps altering the scheme as I did will help (although the original scheme isn't technically wrong).

Quoting my own answer to my linked question on SO:

Analyzing the old approach

First of all, both junction tables given in my question were more than "just" junction tables. They weren't simply a tool to define which elements were related to which elements, but they were something more:

• They also had extra information (the fields "price" and "thoughts", respectively);

• The first one, Company_Product, also had relationships with other tables itself.

This is not technically wrong, strictly speaking, but there is a more natural way to structure the database to represent the same things. And better, with this new approach, making the query I want becomes very simple.

Solution: new approach

The solution rises when we see that we are modeling items that can be purchased and the purchases themselves. Instead of keeping this information "disguised" inside the junction table of a many-to-many relationship, we shall have them as explicit entities in our scheme, with their own tables.

So, first, to clarify, let's rename our models:

• Company stays Company
• Product becomes ProductType
• Company_Product becomes Product
• Person stays Person
• Company_Product_Person becomes Purchase

And then we see that:

• A Product has one Company and one ProductType. Conversely, the same Company can be related to multiple Product and the same ProductType can be related to multiple Product.
• A Purchase has one Product and one Person. Conversely, the same Product can be related to multiple Purchase and the same Product can be related to multiple Person.

Note that there are no many-to-many relationships anymore.

[...]

Instead of using junction tables that were more than simple junction tables, we got rid of the many-to-many relationships and "promoted" the junction tables to full-fledged entities of our scheme. In fact, the tables are the same; the changes were only in the relations and in the way to look at them.