I'm sort of new to creating databases and this is the most complex one I have yet to make and I would like to make sure I am following the best practice in my design. (This is just a private project I'm doing to learn as fun)
我是创建数据库的新手,这是我还没有做过的最复杂的数据库,我想确保我遵循设计中的最佳实践。 (这只是一个私人项目,我正在努力学习)
Basically I will have questionnaires stored which can have multiple questions. Questions can be reused on multiple questionnaires if already created so I have a smart search sort of interface which will check the database.
基本上我会存储可以有多个问题的问卷。如果已经创建了问题,可以在多个问卷上重复使用问题,因此我有一个智能搜索类型的界面来检查数据库。
Then I have an answer for each question linked to a question on a questionnaire and a user who answered it on a date.
然后,我对每个与问卷调查问题相关的问题以及在约会时回答问题的用户提供答案。
I would also have multiple question types from: select, text, text_area, number , date, radio.
我也有多种问题类型:select,text,text_area,number,date,radio。
For select types I would have a choices table indicating the choices available by the select.
对于选择类型,我将有一个选择表,指示选择可用的选项。
User table would link to answers when a questionnaire is being used.
当使用问卷时,用户表将链接到答案。
I would also have questionnaire dependencies for when a question depends on another like: do you smoke? if yes -> how much do you smoke?
当问题依赖于另一个时,我也会有问卷依赖关系:你吸烟吗?如果是 - >你抽多少钱?
Things I'm not really sure about are the many to many relationship using a junction table and self referencing the questions from the junction table to form dependencies. Would this be considered a correct design and if not what am I doing wrong?
我不太确定的事情是使用联结表的多对多关系,并自我引用联结表中的问题以形成依赖关系。这会被认为是一个正确的设计,如果不是我做错了什么?
1 个解决方案
#1
2
In a relational schema, you would represent question dependency through a self-joining foreign key; you don't need to go back to the junction table, since the relationship between the two questions is independent from each question's relationship to the questionnaire.
在关系模式中,您将通过自联接外键表示问题依赖性;您不需要返回联结表,因为这两个问题之间的关系独立于每个问题与问卷的关系。
However, as you've probably noticed, representing a branching set of questions in a relational schema is more than a little awkward. You might want to look into alternatives like MongoDB, which would let you represent a questionnaire as a document containing nested questions. A single user's responses to a questionnaire comprise a second (type of) document; all collected questionnaire results are easily searchable by user or by questionnaire, and you can slice and dig into individual question responses using the aggregation tools. The only thing it doesn't make easier is question reuse -- but odds are that's less than critical.
但是,正如您可能已经注意到的那样,在关系模式中表示一组分支问题不仅有点尴尬。您可能希望查看MongoDB等替代方案,它可以让您将问卷表示为包含嵌套问题的文档。单个用户对问卷的回复包括第二(类型)文档;所有收集的问卷调查结果都可以通过用户或问卷调查轻松搜索,您可以使用聚合工具切片和挖掘个别问题的答案。唯一不容易的问题是重复使用问题 - 但可能性并不重要。
Treating questionnaires and questions as documents also gives you some much more flexible tools for representing dependency: instead of a hard link between "do you smoke?" and "how much?", you might apply a validator to a conditions object, if present, and only ask the question if any conditions are met:
将问卷和问题作为文档处理也可以为您提供一些更灵活的表示依赖的工具:而不是“你吸烟吗?”之间的硬链接。和“多少?”,您可以将验证器应用于条件对象(如果存在),并仅询问是否满足任何条件的问题:
[..., {
"name": "smoker",
"text": "Do you smoke?",
"values": [true, false]
}, {
"name": "how-much",
"text": "How much do you smoke?",
"conditions": {
"smoker": true
},
"values": ["Socially", "< 1 pack/day", "1 pack/day", "2 packs/day", ...]
}, ...]
#1
2
In a relational schema, you would represent question dependency through a self-joining foreign key; you don't need to go back to the junction table, since the relationship between the two questions is independent from each question's relationship to the questionnaire.
在关系模式中,您将通过自联接外键表示问题依赖性;您不需要返回联结表,因为这两个问题之间的关系独立于每个问题与问卷的关系。
However, as you've probably noticed, representing a branching set of questions in a relational schema is more than a little awkward. You might want to look into alternatives like MongoDB, which would let you represent a questionnaire as a document containing nested questions. A single user's responses to a questionnaire comprise a second (type of) document; all collected questionnaire results are easily searchable by user or by questionnaire, and you can slice and dig into individual question responses using the aggregation tools. The only thing it doesn't make easier is question reuse -- but odds are that's less than critical.
但是,正如您可能已经注意到的那样,在关系模式中表示一组分支问题不仅有点尴尬。您可能希望查看MongoDB等替代方案,它可以让您将问卷表示为包含嵌套问题的文档。单个用户对问卷的回复包括第二(类型)文档;所有收集的问卷调查结果都可以通过用户或问卷调查轻松搜索,您可以使用聚合工具切片和挖掘个别问题的答案。唯一不容易的问题是重复使用问题 - 但可能性并不重要。
Treating questionnaires and questions as documents also gives you some much more flexible tools for representing dependency: instead of a hard link between "do you smoke?" and "how much?", you might apply a validator to a conditions object, if present, and only ask the question if any conditions are met:
将问卷和问题作为文档处理也可以为您提供一些更灵活的表示依赖的工具:而不是“你吸烟吗?”之间的硬链接。和“多少?”,您可以将验证器应用于条件对象(如果存在),并仅询问是否满足任何条件的问题:
[..., {
"name": "smoker",
"text": "Do you smoke?",
"values": [true, false]
}, {
"name": "how-much",
"text": "How much do you smoke?",
"conditions": {
"smoker": true
},
"values": ["Socially", "< 1 pack/day", "1 pack/day", "2 packs/day", ...]
}, ...]