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.
Best Answer
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, andPersontables 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.