How to save Large relational data

Hope and pray that you all must well.

开发者_StackOverflow中文版I have a scenario, in which i have to write a very large set of relational/combinational data, I am looking for a implementation technique which must be super fast. Its something like an expert system in AI.

I have 4 entities, Questions, Options, Benefits and Scenarios:

• Each question can have multiple options
• Each option can relate to single question
• On any combination of options a benefit is allocated, the allocation is called scenario
• a scenario can related to any number of options
• a scenario can relate to any number of benefits
• Each benefit can be included in multiple scenarios

Now for instance we look for an example:

• We have 4 questions, q1, q2, q3, q4
• q1 have 3 options q1o1, q1o2, q1o3
• q2 have 4 options q2o1, q2o2,q2o3,q2o4
• q3 have 5 options q3o1, q3o2,q3o3,q3o4, q3o5
• q4 have 2 options q4o1, q4o2
• scenario 1: for combination of [q1o1,q201] a benefit b1 is allocated
• scenario 2: for combination of [q1o1,q201,q303] a benefit b2 is allocated
• scenario 3: for combination of [q201,q304] a benefit b3 is allocated
• scenario 4: for combination of [q304,q401] a benefit b4 is allocated
• scenario 5: for combination of [q402] a benefit b5 is allocated
• scenario 6: for combination of [q1o2,q2o2,q3o1,q4o1] a benefit b5 is allocated

So in this way

• ( (3+1) C 1 x (4+1) C 1 x (5+1) C 1 x (2+1) C 1 ) - 1
• ( 4 x 5 x 6 x 3 ) - 1
• 360 - 1
• 359

scenarios can be build. where as C denote to Combination.

And if questions goes to 25 and each question should have 5 options

• ((5+1) ^ 25 - 1)
• 6 ^ 25 -1
• 28430288029929701375

scenarios can be build

I am looking for a best way to store this relational/combinational data to the database and want to access it back. Will wait for response of you guys.

The following set of tables will do it.

question:
  id
  ...

option:
  id
  question_id
  ...

option_scenario:
  option_id
  scenario_id

scenario:
  id
  option_count
  ...

scenario_benefit:
  scenario_id
  benefit_id

benefit:
  id
  ...

The one thing that is denormalized in the design is that scenario.option_count should be the count of things in option_scenario with that scenario_id.

To query it you'll need to use subqueries heavily. Suppose that person_option is another table with the options a specific person has. Then to find the benefits that that person has you'll need to:

SELECT b.*
FROM (
    SELECT s.scenario_id
    FROM person_option po
      JOIN scenario_option so
        ON so.option_id = po.option_id
      JOIN scenario s
        ON s.id = so.scenario_id
    WHERE po.person_id = ?
    GROUP BY so.scenario_id, s.option_count
    HAVING s.option_count = COUNT(DISTINCT po.option_id)
  ) ps
  JOIN scenario_benefit sb
    ON sb.scenario_id = ps.scenario_id
  JOIN benefit b
    ON b.id = sb.benefit_id