一般网页形式的数据库设计 [英] Database design for general web form

问题描述

我想建立一般的网络表单的后端，其中包含用户可以回答的问题。有多种类型的问题。

• 正常文本问题：问题通过简单的文本字段回答。 示例：一般个人信息，如名称。


• Textarea问题：相同，但是有更多的空间来写答案。 示例：要添加的更多信息框。


• 多项选择问题：具有一些预定义答案的问题，单个可以选择。 示例：您可以如何编程PHP？ []不太好[]平均[]我写了这本书。


• ...（附加问题类型应该可以添加，而不必太多）

用户输入的输入应存储在MySQL数据库中。

问题我这个表单应该是可编辑的。它不会被频繁编辑，但是如果变化，现有的提交不应该受到这些更改的影响。由于这个事实，我认为通过XML文件来配置配置是不够的。

我的方法如下：

• 使用MySQL数据库进行问题的配置（更好的方法？）


• 数据库布局如下所示：
  
  
  
  
  • 问题
    
    
    
    
    • id：INT
    
    
    • 问题：TEXT
    
    
    • 类型：ENUM（'NORMAL'，'TEXTAREA'，'MULTIPLE'）
    
    
    • 活动：BOOL - 如果问题以当前形式使用。如果它不再被使用，并且仅保存在数据库中以与旧提交的兼容性，则为false。
    
    
  
  
  • q_multiplechoice
    
    
    
    
    • id：INT
    
    
    • questionid：INT
    
    
    • 答案：TEXT
    
    
  
  
  • 提交
    
    
    
    
    • id：INT
    
    
    • userid：INT
    
    
  
  
  • submissiondetails
    
    
    
    
    • submissionid：INT
    • 
      
    • questionid：INT
    
    
    • givenanswer：TEXT
    
    
  
  

如您所见，它使用四个表格作为简单的Web表单。我不认为这是在这里使用的最佳方法，并希望您询问是否可以给我一些提示，我应该应用哪些设计更改。

我曾经考虑的其他方法是在提交数据库中存储一个呈现提交的HTML字符串，并使用一个简单的配置文件配置问题。旧的提交不会受到配置更改的影响，因为它们已经被存储在MySql中，这种方法的问题是，当设计发生变化并且提交应该在另一个设计中显示时。）

解决方案

关闭，我们同意否决在数据库中存储HTML的想法，除了可能的几个标签，例如换行符，粗体，强调和下划线，仅供问题的文本使用。如果重点是调查的文本/语义，而不是 查看 详细信息，则定义调查和利用其输出将会更加容易。

为了支持View（layout）配置，CSS可能是机票。这将会将ID作为ID用于将视图决定存储问题的div（或其他html容器）的关键字。几个类名也可以列在问题记录中，但是在CSS中定义。

在提出的数据库模式上，重要的东西似乎在那里。我没有看到提交的回复存储在哪里？是在提交+提交表格？如果是这样，MULTIPLE响应类型在哪里存储，它们是否转换为文本？ （我不认为他们应该，除非我们更喜欢在活动期间修改调查时获取略有不同的价值。）

几个缺失的属性和想法： p>

• MULTIPLE（或其他类型）应该能够支持radio-button类型选择（only-一个）。一种可能的方法是为类型添加一个属性，以定义允许的最大选择数量，一个常见的调查问题：在以下选项中选择3。


• 问题记录可以有一个广告系列或 SurveyID ，允许在同一商店中存储多个调查。


• 如果不太喜欢，一些问题可以推测为以前的布尔型问题。 （如果被调查的人回应不自己的汽车，不要问油更换的频率...）这可以由一个questionID和一个ResponseValue定义（？文本的一般性？）。


• 问题表：添加页数，允许对问题进行分组（除非此信息在问题容器概念中定义，例如调查或广告系列）
• 
  
• 问题表：添加序列号允许以预定义的顺序获取问题（除非这些来自此处未显示的调查/广告系列表）


• q_multiplechoice :(或任何表格列出了给定的多个选择的选项;我怀疑这个表格在该问题中的显示方式）。添加序列号，允许以特定的顺序列出选项。

I want to build a back end of a general web form which contains questions which the user can answer. There are multiple types of questions.

• "Normal" text question: the question is answered via a simple text field. Example: General personal info, like the name.
• Textarea question: the same, but with more space to write the answer. Example: A "More information that you want to add" box.
• Multiple choice question: a question with some predefined answers, from which a single one can be chosen. Example: How well can you program PHP? [ ] not very well [ ] average [ ] I've wrote the book.
• ... (additional question types should be addable without having to hack too much)

The inputs that the users enter should be stored in a MySQL database.

The issue I have is that the form should be editable. It won't be edited very often but when it changes, existing submissions shouldn't be affected by the changes. Due to this fact, I don't think that it is sufficient to make the config via a XML file.

My approach is the following:

• The configuration of the questions is made using the MySQL database (better methods?)
• The database layout is as follows:
  • questions
    • id : INT
    • question : TEXT
    • type : ENUM ('NORMAL', 'TEXTAREA', 'MULTIPLE')
    • active : BOOL - true if the question is used in the current form. false if it isn't used anymore and is only kept in the database for compatibility with old submissions.
  • q_multiplechoice
    • id : INT
    • questionid : INT
    • answer : TEXT
  • submissions
    • id : INT
    • userid : INT
  • submissiondetails
    • submissionid : INT
    • questionid : INT
    • givenanswer : TEXT

As you can see, it uses four tables for a simple web form. I don't think that this is the optimal approach to use here and want you to ask if you could give me some hints on what design changes I should apply.

(other approach I've thought about is to store a HTML string with a rendered submission in the submissions database and to use a simple config file to configure the questions. the old submissions won't be affected by config changes since they were already stored rendered in MySql. Problem with this approach is, when the design changes and the submission should be displayed in another design.)

解决方案

First off, let's agree to veto the idea of storing HTML in the database, except, maybe a few tags such as line-breaks, bold, emphasis and underline, for the question's text only. Defining the surveys and exploiting their output will be so much easier if the focus is on the text/semantics of the survey, rather than View details.

To support View ("layout") configuration CSS could be the ticket. This would key on the ID of the question which would be used as ID to the div (or other html container) where the View decides to store the question. a few class names could also be listed in the question record, but defined in CSS.

On the proposed database schema, the important stuff seems to be there. I don't see however where the submitted responses get stored; is that in submission + submitiondetails tables? If so, where do the MULTIPLE response type get stored, are they converted to text, into givenanswer? (I don't think they should, unless we prefer capturing slightly different values when the survey was modified during the campaign.)

A few missing attributes and ideas:

• MULTIPLE (or another type) should be made able to support "radio-button" type choices ("only-one-of"). a possible way to do this is to add an attribute to the type, to define the max number of choices allowed, a common survey thing: "pick 3 among the following..."
• The question record could have a "campaign" or SurveyID, allowing to store multiple surveys in the same store.
• Without geting too fancy, some questions could be predicated to a previous "boolean" type question. (If polled person respond "no" to own a car, do not ask about the frequency of oil changes...) This could be defined by a questionID and a ResponseValue (?text for genericity?).
• question table: add a 'Page Number', allowing to group the questions (unless this info is defined in a question container concept such as "survey" or "campaign")
• question table: add 'Sequence Number' allowing to get questions in a predefined order (unless such comes from a Survey/campaign table not shown here)
• q_multiplechoice: (or whatever table is where the options for a given MULTIPLE CHOICE are listed; I'm having doubt about this table seeing its fields as showin in the question.) add a Sequence Number, allowing to list the options in a particular order.

这篇关于一般网页形式的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！