-2

I end up with complex db design issue of user's question/answer & thought to take your help or suggestions. It looks it's rare case which generally appear.

A User should be able to provide offered question's answer (Up to here looks easy & good). Now we have a kind of requirement where we would offering N-levels of nested questions.

Let me explain it by an example, 

Question - 1 : What's your favourite food ? (Very simplest - level -1)
Question - 2 : Do you like football match ?  (Level - 1 )
               Yes ? No ? (Level-1.1 or call it 2nd nested level)
                         if No then Why ? (Level - 1.1.1 or call it 3nd nested level)

So likewise, as of now we have up to 5-nested level of question bank and for which I want your opinion/suggestion about how do we do this?

Any suggestion would be much appreciable !!

Vishal Gajera
  • 4,137
  • 5
  • 28
  • 55
  • Does this answer your question? [What are the options for storing hierarchical data in a relational database?](https://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database) – philipxy Dec 27 '19 at 10:54
  • This is a faq. Before considering posting please always google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. – philipxy Dec 27 '19 at 10:55

1 Answers1

2

You just need to have a ParentQuestionId on the Question table. This can be NULL for top level questions.

eg:

Question Id: 1
ParentId: NULL
Text: Do you like football match?

Question Id: 2
ParentId: 1
Text: If No then Why?

This allows unlimited nesting, and unlimited questions per level assuming all questions have at most one parent, which seems to make sense to me...

This is a standard Tree Hierarchy so you should be able to find examples for this design and queries supporting it quite easily.

Milney
  • 6,253
  • 2
  • 19
  • 33