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

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.