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

  • Maybe I'm wrong but, sounds like a binary tree to me. Despite the statement say "has many", generic trees can be encoded as binary. In this case, each node has either a solution (left) or a problem (right) and each root can be either of both too. – Laiv Nov 19 '18 at 8:07

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.

  • thanks a lot for your answer, I only have one problem with it, which is the data presentation .... I need to show the results as a hierarchical structure of related "SOLUTION"s and "PROBLEM"s , I doubt that the 2 joins , the union to unify the appearance, and the ordering will be of acceptable performance when the data grows large – osama yaccoub Nov 21 '18 at 13:38
  • @osamayaccoub this is a problem that you will always have for hierarchically structured data (here it's even worse: it might even be a mesh instead of a hierarchy). But it's the only way to solve the problem accurately. Now about perdormance: data can grow large, but RDBMS will still work at good performance thanks to the indexes it uses for the join (it doesn't have to read all the records to find the matching ones). I'd even pretend that the RDBMS optimizer will in most cases do a better job than a human optimized query. – Christophe Nov 21 '18 at 13:45
  • I am still checking other options, now I started thinking again to put both in one table (as discussion item) and have a column to discriminate problem from solution ... they have some common fields like start date, end date, description and status (though the status value is different for both) ... for for fields special for any type I can add a column which will be optionally filled ... I am afraid that this violates the separation of concerns principle .... – osama yaccoub Nov 24 '18 at 13:51
  • It will be like your second option but without the two tables in the middle ... I can also discard the fourth table and use adjacency list or path enumeration – osama yaccoub Nov 24 '18 at 13:52
  • @osamayaccoub the fourth table implements the adjacency list. So, unless you're using a nosql db, you'll need to keep it (at least if you want to keep reasonable performance and not start parsing strings across a lot of elements. – Christophe Nov 24 '18 at 13:59

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".

  • the problem is that, in addition to the above, I have also : A has zero or more As and B has zero or more Bs – osama yaccoub Aug 21 '19 at 0:07
  • @osamayaccoub: That's fine. Both "A" and "B" are placeholders where you can replace them with any two entities or table names. I was just giving general examples of patterns I see before moving into an example that is specific to your problem. The important thing to learn from this is to identify the pattern and not focus so much on the concrete names of things. – Greg Burghardt Aug 21 '19 at 11:27
  • I mean that I will need A1, A2, B1, B2 in my case rather than just simply A&B, or I can keep it A,B and add a new discriminator column to specify source table and target table, my concern with both is the performance in tracking the whole thread of related As and Bs (If I do understand correctly your answer) – osama yaccoub Aug 23 '19 at 2:53

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.