0

I have an application, which contains two Models Question and Option

  • Each Question has many Options
  • Each Option may have many Questions (and this Question may have many sub Options) so on and so forth.

Please help in how to achieve this relationship in database. Thank You.

1

I would handle it like a normal many-to-many relationship, creating a table that holds question_id and option_id. Then, since you can have sub options, i would add another field containing the primary key of this table:

CREATE TABLE [question_option_rel] (
    [id] int IDENTITY (0,1) PRIMARY KEY,
    [id_question] INT FOREIGN KEY REFERENCES [question]([id]),
    [id_option] INT FOREIGN KEY REFERENCES [option]([id]),
    [parent_option] INT FOREIGN KEY REFERENCES [question_option_rel]([id])
)
| improve this answer | |
  • here does the question id mean that it is the sub question belonging to the option or the option belongs to the main question? – linktoahref Oct 22 '18 at 16:08
  • I don't think i get what you're saying, but to be more clear: [parent_option] is a foreign key referring the primary key itself. When it's null it means that the record is a primary option (and you can have many of them). When it's valued, it means that the current record is a sub-option – Mattia Nocerino Oct 23 '18 at 7:11

Not the answer you're looking for? Browse other questions tagged or ask your own question.