At present, the table structure of a question bank is designed, and one question will have subquestions, the amount of evaluation table data exceeds 10 million, SQL Server is used as storage.
Question table contains question number, stem, type, explanation, answer and other attributes.
what is the best way to design the schema in this situation?
First way:
"Question" Table
============================================================
qid | parentId | qtype | sterm | anwser | childQty | etc..
============================================================
Second Way
"Question" Table
===============================================
qid | qtype | sterm | anwser | childQty | etc..
==============================================
"Sub Question" Table
=============================================
subid | qid | qtype | sterm | anwser | etc..
=============================================
Third Way
"Question" Table All question should be kept in a Question table,
=================================================
qid | qtype | sterm | anwser | childQty | etc..
=================================================
"QuestionRelationships" Table
=============
qid | subid
=============
Which one is preferable?
>Solution :
The third way is the preferable way to design the schema in this situation. This is because it uses a normalized approach, which helps to reduce data redundancy and improve data integrity.
The first way is not preferable because it stores the parent ID in the question table, which can lead to data redundancy if there are multiple subquestions for a question.
The second way is also not preferable because it creates two separate tables for questions and subquestions, which can make it difficult to query the data.
so you can prefer the third way.