User configurable forms with php and mysql

Question

An online application we are building (php & mysql) requires users to be able to create their own forms for data capture and record this data in a database, respecting the existing ORM's.

If the forms where "hard coded" then we would simply set the db tables up to store the normalised data ourselves however as our users define the form fields contained in the forms, we're not sure what is the best way to proceed to implement this functionality.

Do we need to think about some kind of meta data or data abstraction layer for our DB? Google hasn't been too much help as we're unsure about how we need to go about this.

Any pointers in the right direction would be gratefully appreciated!

Answer 1

Many content management systems address this problem in different ways.

For example, in Drupal, users can create their own custom content (with custom forms) through the CCK module. The module defines different types of fields that the user can create, then generates tables with specific data types to store the data.

Some tips:

1. Define your field types - Think about giving the users a choice of different field types (e.g., select box, string, radio).

2. Create tables for user defined fields - Each field type will have a specific SQL data type. Define a table using these data types. For example, a select box might be mapped to an enum and a input text element might be mapped to a varchar column.

3. Add data to the new tables - use the new tables to store the data in a somewhat normalized way.

Obviously there are many different approaches, but these are just a few suggestions.

Answer 2

Thanks for your response, could you elaborate more on creating the tables for user defined fields? I've created a table to store the form fields associated with a form ie. -

Table nane: form_fields

Columns -

id

form_id // foregin key linking to associated form

label // label for the form field

field_name // variable name for the field

field_type // what type of field it is ie. textfield

I'm just a little unsure as to how I create the tables to store the data associated with these form fields. Could you give me some example table set ups please? Once again any help is really appreciated!!

Answer 3

I think I've found a solution to my problem, so for all those people who come along a similar problem have a look at the following artcles -

http://www.adaniels.nl/articles/an-alternative-way-of-eav-modeling/

http://weblogs.sqlteam.com/davidm/articles/12117.aspx

Hope this helps.