在1个或多个表中存储单个表单表问题 [英] Storing single form table questions in 1 or multiple tables

问题描述

我已经在Web应用程序内部编写ASP.NET表单很长时间了.通常，大多数Web应用程序都有一个用户登录，选择要填写的表格并回答问题，因此您的表格看起来像这样

I have been coding ASP.NET forms inside web applications for a long time now. Generally most web apps have a user that logs in, picks a form to fill out and answers questions so your table looks like this

Table: tblInspectionForm
Fields:
inspectionformid (either autoint or guid)
userid (user ID who entered it)
datestamp (added, modified, whatever)
Question1Answer: boolean (maybe a yes/no)
Question2Answer: int (maybe foreign key for sub table 1 with dropdown values)
Question3Answer: int (foreign key for sub table 2 with dropdown values)

如果我没记错的话，它既符合第二范式也适用于第三范式.您没有在表中存储用户名，仅在ID中存储用户名.您不会在Q-3中存储下拉列表或是/否"值，而不会存储其他表的ID.

If I'm not mistaken it meets both 2nd and 3rd normal forms. You're not storing user names in the tables, just the ID's. You aren't storing the dropdown or "yes/no" values in Q-3, just ID's of other tables.

但是， IF 所有问题都是完全相同的数据类型(假设没有Q1或Q1也是一个整数)，它们链接到完全相同的外键(例如，一个具有20个字符的表格)都是1-10的问题，或者有相同的答案可供选择)，这样做会更好吗?

However, IF all the questions are exactly the same data type (assume there's no Q1 or Q1 is also an int), which link to the exact same foreign key (e.g. a form that has 20 questions, all on a 1-10 scale or have the same answers to chose from), would it be better to do something like this?

so .. Table: tblInspectionForm
userid (user ID who entered it)
datestamp (added, modified, whatever)
... and that's it for table 1 .. then

Table2: tblInspectionAnswers
inspectionformid (composite key that links back to table1 record)
userid (composite key that links back to table1 record)
datastamp  (composite key that links back to table1 record)
QuestionIDNumber: int (question 1, question 2, question3)
QuestionAnswer: int (foreign key)

这不仅适用于对单个表单仅具有相同类型答案的表单.也许您的表单在1-10个评分(int)中有10个，在布尔值问题中有10个，然后是10个自由格式..您可以将其分为三个表.

This wouldn't just apply to forms that only have the same types of answers for a single form. Maybe your form has 10 of these 1-10 ratings (int), 10 boolean-valued questions, and then 10 freeform.. You could break it into three tables.

缺点是，保存表单时，您要为表单上的每个问题拨打1个电话.好处是，如果您有大量夜间整合或复制操作可以提取数据，并且您决定添加新问题，则无需手动修改任何复制报告数据源或旨在读取数据的任何其他内容/查询您的表单数据.如果您最初有20个问题，并且将更改部署到应用程序中添加了第21个问题，则该更改将自动进入任何外部复制，数据源中，并报告查询该数据的信息.另一个优点是，如果您有一个非常长的时间(在房地产行业中，当您的检查表中有100多个问题超过表格行的8k限制时，这可能会发生很多)，您最终不会遇到问题

The disadvantage would be that when you save the form, you're making 1 call for every question on your form. The upside is, if you have a lot of nightly integrations or replications that pull your data, if you decide to add a new question, you don't have to manually modify any replications to reporting data sources or anything else that's been designed to read/query your form data. If you originally had 20 questions and you deploy a change to your app that adds a 21st, it will automatically get pulled into any outside replications, data sources, reporting that queries this data. Another advantage is that if you have a REALLY LONG (this happens a lot maybe in the real estate industry when you have inspection forms with 100's of questions that go beyond the 8k limit for a table row) you won't end up running into problems.

这种情况曾经是保存表单数据的首选方法吗?

Would this kind of scenario ever been the preferred way of saving form data?

推荐答案

作为经验法则，每当看到一组名称中带有数字的列时，就知道数据库的设计欠佳.
在大多数情况下，您想要做的是拥有一个表格/调查表，一个问题表，一个潜在答案表(用于多项选择题)和一个用户选择答案的表.
您可能还需要一个用于问题类型的表格(即自由文本，多项选择，是/否).基本上，架构应如下所示:

As a rule of thumb, whenever you see a set of columns with numbers in their names, you know the database is poorly designed.
What you want to do in most cases is have a table for the form / questionnaire, a table for the questions, a table for the potential answers (for multiple-choice questions), and a table for answers that the user chooses.
You might also need a table for question type (i.e free-text, multiple-choice, yes/no). Basically, the schema should look like this:

create table Forms
(
    id int identity(1,1) not null primary key,
    name varchar(100) not null, -- with a unique index
    -- other form related fields here
)
create table QuestionTypes
(
    id int identity(1,1) not null primary key,
    name varchar(100) not null, -- with a unique index
)

create table Questions
(
    id int identity(1,1) not null primary key,
    form_id int not null foreign key references Forms(id),
    type_id int not null foreign key references QuestionTypes(id),
    content varchar(1000)
)

create table Answers
(
    id int identity(1,1) not null primary key,
    question_id int not null foreign key references Questions(id),
    content varchar(1000)
    -- For quizez, unremark the next row:
    -- isCorrect bit not null
)

create table Results
{
    id int identity(1,1) not null primary key,
    form_id int not null foreign key references Forms(id)
    -- in case only registered users can fill the form, unremark the next row
    --user_id int not null foreign key references Users(id), 
}

create table UserAnswers
(
    result_id int not null foreign key references Results(id), 
    question_id int not null foreign key references Questions(id), 
    answer_id int not null foreign key references Answers(id),
    content varchar(1000) null -- for free text questions
)

此设计在生成表单时将需要一些联接(如果每个应用程序有多个表单，则只需添加一个表单可以引用的应用程序表)，并通过一些联接来获得结果，但这是最好的方法我知道动态表单数据库设计.

This design will require a few joins when generating the forms (and if you have multiple forms per application, you just add an application table that the form can reference), and a few joins to get the results, but it's the best dynamic forms database design I know.

这篇关于在1个或多个表中存储单个表单表问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！