what's the best implemention of client creatable and modifiable web forms in a relational database?

Question

In several web application projects I've been a part of, the client asks to be able to create their own forms. The question arises on how to store their form definitions, and then how to store user inputted values into those custom forms.

I've seen it done two ways:

1. Assuming that the client only defines how many fields, and what labels are associated with those fields; we can come to a solution involving four tables. FormDefinition, FormFieldDefinition, FormInstances, FormFieldValues. The client makes changes to FormDefinition and FormFieldDefinition, and the web app uses that information to render an HTML web form, on which the website visitor (end user) will submit the form, in which a new row in FormInstances is created and the values are saved in the FormFieldValues table.

   Rows in FormDefinition defines the form, i.e. form definition ID = 2, form title = 'Car Registration Form'. Rows in FormFieldDefinition defines fields of a form in FormDefinition, i.e. field definition ID = 7, field label = 'Car Model', field type = 'varchar(50)'. Rows in FormInstance is an instance of each form filled out by a user, i.e. definition id = 2, date_entered = '2008-09-24'. And rows in FormFieldValues are entries by the user, i.e. field definition = 7, value = 'Tiburon'.

   Unfortunately, it means the value column in FormFieldValues must be a char type of the largest possible size that your client might specify in a web form... and when form definitions change, managing old data becomes iffy. But user entries are queryable (i wrote a quick query that lists user entries given a form id, which is similar to another pivot question).

2. An alternative to using four tables would be to serialize the form definitions and user's form entries into XML (or YAML or something similar) and store that as text. The upside is that the forms are human readable in the database. The downside is that there will be more application overhead with parsing XML, and the database becomes much less queryable from an SQL standpoint.

My real question is, what is this database model called? (So I can google this problem.) But I would settle on an answer to: which is the better implementation or are there better (or just as good) implementations out there?

Answer 1

what is this database model called?

Not sure what to actually call it, but it's the same process used in dynamically generating surveys. If you look for any source for generating survey applications you'll find the same general database schema as well as similar methods.

Also look into form builder sites, like http://wufoo.com/ or http://frevvo.com/ for UI ideas (if you're making it web based).

Answer 2

There is a third option, where you create tables and add columns if needed. It depends on how many forms are created, but databases can handle easily a lot of tables. So if a user wants to add a form 'Car Registration Form', you add a table 'CarRegistrationForm'. For every field they want on the form, you can let them choose between some basic types as date, int and text. And when text is chosen, they have to enter a maximum length from a pick list, which gives you info if the field should be a varchar or a clob.

This works on SQL Server, where you can easily add and drop columns. For DB2 it 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 the fields on it in two separate tables.

Answer 3

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

This leads to a bunch of complex problems like making sure each form instance includes the same set of fields, or making sure mandatory fields are filled out (the equivalent of NOT NULL in a conventional table).

You asked how best to do this with a relational database. In a relational database, you should use metadata for metadata. In this case, it means creating new table(s) for each form, and use columns for form fields. So your form definition is simply the table metadata, and one form instance is one row in that table. If you support forms with multi-valued answers (e.g. checkboxes), you need dependent tables too.

This might seem expensive or hard to scale. Probably true. So a relational database might not be the best tool for this job. You mentioned the possibility of XML or YAML; basically some kind of structured file format that you can define ad hoc. You should define a DTD for each client's form, so each form collected can be validated.

edit: If you really need the flexibility of EAV in your application, that's fine, there are usually circumstances that justify breaking the rules. Just be aware of the extra work it takes, and plan for it in your development schedule and as you scale your server to handle the load. Also see another answer of mine about EAV on StackOverflow.

Answer 4

You probably want Entity-Relationship Model

There are indeed GUI tools to create schema interactively from such models.