如何设计这两张桌子？ [英] How to design these two tables?

问题描述

我想构建一个允许用户构建自己的表单的应用程序。

用户可以在其表单中输入数据并查询数据。表单可以在创建和使用后进行编辑（从中添加/删除字段）。该表单可以有小文本框，大文本框，单列表，复选框或下拉菜单。

I want to build an application that allows a user to build its own form(s).
A user can input data into its form and query the data too. The form can be edited after being created and used ( add/remove fields from it ). The form can have small text box, big text box, single list, check box, or a drop down menu.

我一直在阅读并询问哪种方法更好的建立这种
的数据库，我没有直接/好的答案。我读到在这种应用中，基于列的设计（并将每个字段表示为一列）不是一个好主意，因为用户可以添加许多字段，这不是一个好主意，因为应该有一个限制表中的列数。但是，我不能提前知道用户将添加多少字段。 （我不知道他们的意思，如果你进一步解释一下就会有很多帮助）。

I've been reading and asking around about which approach is better for building this kind of databases and I got no straight/good answer. I read that in this kind of application, the column based design (and representing each field as a column) is not a good idea since a user can be adding many fields and this is not a good idea, since there should be a limit on the number of columns in a table. However, I can't know in advance how many fields the user will add. (I'm not sure what they mean. It will help a lot if you explain further by giving an example.)

我认为一个好主意可能是将字段根据其类型。 （类型1：
文本框，类型2：下拉列表，类型3：单列表...等等）。我想设计我的表，以便我可以升级我的应用程序，并添加一个新的类型，任何时候我想要没有改变我的模式。所以我想要有2个表，一个表保存关于该字段的元数据：其类型，其名称，其位置可以以表格和另一个表保存该字段的实际值。我似乎不知道这些表中究竟有什么，以及如何正确设计它们。

I thought a good idea might be to group the fields according to their types. (type 1: text box , type 2: drop down list, type 3 : single list... and so on). I want to design my tables so that I can upgrade my application and add a new type any time I want without ALTERING my schema. So I was thinking to have 2 tables, one table to hold the metadata about the field: its type, its name, its position maybe in the form and another table to hold the actual value of the field. I can't seem to know what exactly to have in these tables and how to design them correctly. Your help is highly appreciated.

推荐答案

**Form**     A Form Template
----
formID (PK)
authorID
Name



**FormField**    =  1 row for each new question on the form
----------   
FormFieldID (PK)
FormID   (FK)
FieldID  (FK)
SortOrder  INT


**FormFieldOptions**     - if the form field is a selectbox this will store the options availble to pick from
---------------
FormFieldID   (fk)
Name
SortOrder  INT


**Field**            -- The will be Text Short, Text Long, Checkbox, Radiobutton, Selectbox etc
------
FieldID   (PK)
Name      
TYPE    

这是用于创建表单。为了保存用户答复/回复我将为读者留下的表格;）

That is for creating forms. For saving users answers/responses to the form I will leave for an exersise for the reader ;)

这篇关于如何设计这两张桌子？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！