Schema help needed

tunage

Hello all..

Im designing a database to store system problem troubleshooting (like a flow chart).

Basically there is a SYSTEMS and PROBLEMS table and a SYSTEMS_PROBLEMS linking table (many system can have many problems).

Each "problem" starts with a question, which can either go to an answer or to another question. My problem is how to store the info of when a question goes to 2 questions (Q3 below)

Each problem can have several questions in it. PROBLEM_QA stores their relationship to each other, so if a question goes to 2 more questions - those questions are stored in Next_Question1_ID and Next_Question2_ID ( Answer_ID would be empty).

Likewise Next_Question2_ID would be empty if a question went to another question and an answer.

Q1 → A1
↓
Q2 → Q3 → Q4 → A2
↓ ← ↓ ← ↓
↓
Q5 → Q6 → A3
↓ ← ↓
↓
A4

These are my tables

SYSTEMS
System_ID (pk)
Description

PROBLEMS
Problem_ID (pk)
Description

SYSTEMS_PROBLEMS
System_ID (pk)
Problem_ID (pk)

PROBLEM_QA
Problem_ID (pk)
Question_ID (pk)
Answer_ID
Next_Question1_ID
Next_Question2_ID

QUESTION
QuestionID (pk)
Question

ANSWER
AnswerID (pk)
Answer

This appears to work ok as it is, but im not too happy with the PROBLEM_QA table, as a lot of fields will be left blank, any suggestions on how it could be stored differently? Could it be normalizeed further?

Thanks in advance

Najjar

Store a question_number in the db as follows
1.0.0.0, 1.1.0.0, 1.2.0.0, etc...
2.0.0.0, 2.1.0.0, 2.2.0.0, etc...
etc...

This way you can relate/group all questions as needed
I would do the same for the problems
You can break these string in your code to determine which is which.
I hope this helps

tunage

Thanks for your reply but im not really sure how that would work, wouldnt it be better stored as seperate fields rater than a comma seperated list?

I think ive come up with a better solution than my first which would be to change the PROBLEM_QA table to this -

PROBLEM_QA
Problem_ID (pk)
Question_ID (pk)
Yes_ID
No_ID

Each question has a YES or NO option. These can either go to a question or an answer, so a question can go to either -

a) Answer - Answer
b) Answer - Question
c) Question - Question

The Yes_ID and No_ID fields would store the ID of either the next question or next answer.

Becuase the answer and question IDs are autoincrements i though i could just prepend "a" or "q" to the id. (eg q1, q2, q3, a1, a2, a3)

The only concern i have about this would be that it would make querying MySQL a bit trickier, but so far this is the best (easiest?) solution i can think of.

Another option would be to just have a QA table that recorded all questions and answers instead of seperate tables. This would appear to be easier but im not sure if it would be better to have seperate tables.

QA
Qa_ID (pk)
Description
Type (enum - Q / A )

Any thoughts?