Data model for a extensible web form

Question

Suppose that I have a form that contains three 10 fields: field1..field10. I store the form data in one or more database tables, probably using 10 database columns.

Now suppose a few months later that I want to add 3 more fields. And in the future I may add/delete fields from this form based on changing requirements. If I have a database column per form field, then I would have to make the corresponding changes to the database each time I change the form. This seems like a maintenance headache. There must be a more sophisticated way.

So my question is, how do I design a data model that is loosely coupled with my UI? A concrete use case is a CRM system that is extensible/customizable by users.

Answer 1

You could abstract fields to a separate table so that they are many-to-many to the Form table:

Form

ID
Name
etc.

Field

ID
Label
Value

FormField

FormID
FieldID

Answer 2

Unless you have a really good reason to do this, then this generally is a bad idea. It makes it very difficult to optimize and scale the database.

If you absolutely must do it, then Travis's suggestion is fine for small tables, but its not really going to scale that well.

Answer 3

My team came up with a solution for this when I worked for Quest Computing on AIMS (www.totalaims.com). In summary we added maintenance screens that allowed the administrator to add metadata and also as a result add fields to the database in certain tables. The fields were also added to their own maintenance and search screens automatically. We built this on top of the OpenACS. You can find out more at www.openacs.org - search for "flexbase" or "dynfields" or look here www.project-open.org/doc/intranet-dynfield/ . This worked quite well - them main downside being a side effect of the main upside i.e. that addition of fields could be done by non-DBAs and as a result performance could easily be compromised.

Answer 4

I have done this in the past using an XML column in the database to store the extra fields. I generally just have a big property bag in the XML column and then use XSD to enforce validation when doing updates or inserts. When I am retrieving the data I have rules in XSL or the object model that determine if the element is displayed, what additional formatting to apply and for web forms what type of input element to use based on the type of data in the property node.

It works really well if there is a need to have some data stored relationally and other data stored extensibly to avoid the wide table with lots of null rows effect.

If you don't need to do relational things with the data, such as joining or pivoting with other tables in the database, then a simple self contained XML form is a good solution as well.

Most databases now have first class XML support for this sort of thing. In SQL Server for example you can apply an XSD schema to a column of an XML datatype right in the datbase. In recent versions there is also support for indexing on those columns.