Skip to main content

how to model 2 objects that refer to each other in the DB [Resolved]

Now I have 2 objects which are "PROBLEM" and "SOLUTION", obviously "SOLUTION" is a proposed solution to a given "PROBLEM", my problem is that in my business I have the following relations:

  1. a "PROBLEM" can refer to one or more "SOLUTION" (i.e. a "PROBLEM" that happened due to a "SOLUTION"),

  2. "PROBLEM" can refer to one or more "PROBLEM" (i.e. a "PROBLEM related to a another "PROBLEM"),

  3. "SOLUTION" can refer to one or more "PROBLEM" (i.e.a proposed "SOLUTION" to a "PROBLEM")

  4. "SOLUTION" can refer to one or more "SOLUTION" (i.e. a "SOLUTION" built on top of another "SOLUTION")

I wonder how I can model this business in a RDBMS, one solution I am thinking of is to put both "SOLUTION" and "PROBLEM" in the same table, but actually they are totally different objects

I know that the word "one or more" in the previous cases mean many to many relation, but how can I tell that the relation is either pointing to a "PROBLEM" or a "SOLUTION", also how can I order it in a performant way (2 "SOLUTION"s are related to the same "PROBLEM" , but one came before the other)

important note : performance is a concern, I don't want many joins or sophisticated logic that brings the application to its knees


Question Credit: osama yaccoub
Question Reference
Asked August 24, 2019
Posted Under: Programming
23 views
2 Answers

Step 1: Requirements first

It makes no sense to optimize a solution if it's wrong from the start. So you need to implement three many-to-many relations (between solution and problems, in between problems, and in between solutions).

So whether you like it or not, you'll need to have a couple of joins, because many- to-many will require at least three tables. One single table for both will fail to meet the requirements (only possible for one-to-one relations)

Option 1:

  • Table with problems (primary key: problem id)
  • Table with solutions (primary key: solution id)
  • Many to many relation with pairs of problem id and solution id.
  • Many to many relation with pairs of related problems
  • Many to many relation with pairs of solution

Option 2:

  • Table of unique problem and solution id, that for each id says if it's a problem or a solution
  • Table of problem descriptions (only entries for id corresponding to problems)
  • Table of solution descriptions (only entries for id corresponding to solutions)
  • Relationship table containing pairs of related ids (whether problems or solutions)

In all the case, you can create a problem, then a solution, then a link between both.

Step 2: Optimize

Only after having written the necessary joins, will you be able to optimize.

But first you need to find out if it's a problem at all. Because if your tables have indexes on the ids, and with the power of modern RDBMS optimizers, you'll barely have a performance impact.


credit: Christophe
Answered August 24, 2019

When you see any of these patterns:

A refers to one or more B
A refers to zero or more B
A has many B
A has zero or more B

You should create table "C" which has foreign keys to both "A" and "B":

+======================+
| Table C              |
+----------------------+
| id (PK)              |
+----------------------+
| a_id (FK to table A) |
| b_id (FK to table B) |
+----------------------+

Just replace "A" with "PROBLEM" and "B" with "SOLUTION" to create table C called "PROPOSED_SOLUTIONS".

Then replace "A" with "PROBLEM" and "B" with "PROBLEM" to create table C called "RELATED_PROBLEMS".

Then replace "A" with "SOLUTION" and "B" with "SOLUTION to create table C called "RELATED_SOLUTIONS".


credit: Greg Burghardt
Answered August 24, 2019
Your Answer