What is the best implementation of client created and modified web forms in a relational database?

Question

In several web application projects that I participated in, the client asks to create their own forms. The question is how to store your form definitions, and then how to store user-entered values ​​in these custom forms.

I saw this in two ways:

• Assuming that the client determines only the number of fields and which labels are associated with these fields; we can come up with a solution that includes four tables. FormDefinition , FormFieldDefinition , FormInstances , FormFieldValues . The client makes changes to FormDefinition and FormFieldDefinition , and the web application uses this information to display the HTML web form on which the site visitor (end user) will submit a form in which the new line in FormInstances and the values ​​are stored in the FormFieldValues table.

  Lines in FormDefinition define the form, i.e. form definition ID = 2, form title = 'Car Registration Form' . The lines in FormFieldDefinition define the form fields in FormDefinition , i.e. field definition ID = 7, field label = 'Car Model', field type = 'varchar(50)' . The lines in FormInstance are an instance of each form filled in by the user, i.e. definition id = 2, date_entered = '2008-09-24' . And the lines in FormFieldValues are user records, i.e. field definition = 7, value = 'Tiburon' .

  Unfortunately, this means that the column of values ​​in FormFieldValues must be the char type of the maximum possible size that your client can specify in the web form ... and when changes in the forms change, managing the old data becomes iffy. But user records are requested (I wrote a quick query that lists user records with a form identifier that looks like another supporting question ).

• An alternative to using four tables would be to serialize form definitions and write custom form entries in XML (or YAML or something similar) and save it as text. The surface is that forms are read by a person in a database. The downside is that XML parsing will have more application overhead, and the database will become much less accessible from an SQL perspective.

My real question is: what is the name of this database model? (So ​​I can solve this problem.) But I would decide to answer the question: which one is better, or are there any better (or just good) implementations?

Answer 1

What you describe is often called "Entity-Attribute-Value" and is sometimes described as "mixing data and metadata." That is, the names of the attributes (fields) are stored as strings (data).

This leads to many complex problems, such as checking that each instance of the form contains the same set of fields or filling out the required fields (NOT NOT NULL equivalent in a regular table).

You asked how best to do this with a relational database. In a relational database, you must use metadata for metadata. In this case, this means creating a new table (s) for each form and using columns for the form fields. So your form definition is just the metadata of the table, and one instance of the form is one row in that table. If you support forms with multiple answers (e.g. checkboxes), you also need dependent tables.

This may seem expensive or difficult to scale. Probably true. Thus, a relational database may not be the best tool for this work. You mentioned the possibility of XML or YAML; basically some sort of structured file format that you can define ad hoc. You must define a DTD for each client form so that each assembled form can be validated.

edit: If you really need EAV flexibility in your application, this is normal, usually there are circumstances that justify breaking the rules. Just keep in mind the extra work that it does, and plan it in your development schedule and scale the server to handle the load. Also see Another answer to my question about EAV in StackOverflow.

Answer 2

what is called this database model?

Not sure what to actually call it, but it's the same process that is used to dynamically create surveys. If you are looking for any source for creating polling applications, you will find the same general database schema as similar methods.

Also look at form builder sites such as http://wufoo.com/ or http://frevvo.com/ for user interface ideas (if you are doing this based on the Internet).

Answer 3

There is a third option where you create tables and add columns if necessary. It depends on how many forms are created, but databases can easily handle many tables. Therefore, if the user wants to add the "Car Registration Form" form, you add the "CarRegistrationForm" table. For each field that they want in the form, you can let them choose between some basic types like date, int and text. And when the text is selected, they should enter the maximum length from the selection list, which gives you information if the field should be varchar or clob.

This works on SQL Server, where you can easily add and remove columns. For DB2, this can be a problem because the drop column is not implemented. For mysql, I'm not sure.

You still need to register your forms and fields in it in two separate tables.

Answer 4

You probably need an Entity-Object Model

There are indeed GUI tools for interactively creating diagrams from such models.