3

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.

  • Few things in software development are actually "standard;" the C# specification is one of them (it being an ECMA standard). Did you really mean "commonly-accepted?" – Robert Harvey Feb 9 '18 at 20:21
  • Your database schema seems reasonable. The JSON arrangement you came up with looks like a "reporting" problem to me (i.e. it is constructed output). You just need to create the correct logic to construct it. The only reason you might have trouble is if you want it to be reversible; that is, to reconstruct the original entities from the resulting JSON. But even then, you have all the Id's. – Robert Harvey Feb 10 '18 at 1:13
  • @RobertHarvey Thanks for the fast reply. I am not sure I follow your reasoning, what exactly is a "reporting problem"? As I mention in my linked question, I am looking for the "right way" to do it (because I already have in mind a very inefficient way, both in memory and time, so I was wondering if sequelize can do it or not). But that's a discussion for the linked question, not this one. If you can expand your last comment in an answer here, that would be great. (continued) – Pedro A Feb 10 '18 at 1:21
  • @RobertHarvey If I understand correctly, you're basically saying that "it seems to be acceptable", although the fact that you needed the real-life example to think more about it is an evidence that it is not "clearly acceptable at the first glance". – Pedro A Feb 10 '18 at 1:22
  • No, it's evidence that one can't make a reasonable judgement without more specifics. It's like asking how many numbers one should put on a dice if one doesn't know how many sides the dice has. – Robert Harvey Feb 10 '18 at 1:23
4

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.

1

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.

  • Thanks for your answer. As for your last sentence, don't I lose some information by doing that? In my situation, both junction tables mentioned in my question have other important fields (not only the IDs), and if I follow your suggestion of a making ABC depend on A, B, and C directly, looks like I can't store the extraField that existed in AB anymore. Is there a solution to that? – Pedro A Feb 9 '18 at 20:17
  • Welcome. Why, of course you can store extra fields: create table ABC (a_id, b_id, c_id, extra_field, foreign key (a_id) references A(id),...). The idea is that you reference A and B directly for FKs, not AB. – 9000 Feb 9 '18 at 20:51
  • I've updated my question, please take a look. I wonder that if I create a table as you're suggesting, I can't store the "price" field in any meaningful way anymore (or perhaps I'm missing something, hopefully(?)). Thanks again. – Pedro A Feb 10 '18 at 1:07
  • I upvote and endorse the answer by @derek-elkins above. – 9000 Feb 10 '18 at 18:50
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.