Database design for custom form builder (and storage of results)

Question

I'm trying to implement a custom form builder, similar to those provided by Wufoo and Google.

While I've created a simple UI to create these custom forms with, my issues lie in the database design. Upon creating the form, a JSON implementation is saved in the database (would like to improve this) and referenced to build the form that a user would see.

Upon submission, I'd like to store all of the fields of the form in a database. Following the JSON structure used for designing the database, this is easy enough. However I'd like each individual field to be searchable.

The questions:

1. Is there a better way to store the form design?
2. What data structures/models would fit storage of the form's results? I've seen that EAV could be a possiblity, but due to the different input types involved (drop-down, checkbox, text, textarea) this may become tedious.
3. Which structure would allow easiest searching and permit the use of WHERE clauses? The JSON example given, doesn't allow me to do this quite as nicely

Answer 1

EAV is a valid option - it can be complex and awkward with what effectively becomes untyped data.

XML with XPath would also be an option: http://dev.mysql.com/tech-resources/articles/xml-in-mysql5.1-6.0.html

I would think that most cases would require you to generate dynamic SQL if the fields to search can vary.

Answer 2

EAV works quite well since you can often map map values into a few basic types. In a personal project we have a table with :

entity_id     : INTEGER REFERENCES entities(id)
attr_id       : INTEGER REFERENCES attributes(id)
value_bool    : BOOLEAN
value_int     : INTEGER
value_string  : VARCHAR
value_text    : TEXT

And the infos about attr_id is stored in a different table where we can find the attribute type and name and such. Also the difference between string and text is that text can have a 'full text' search index on it while string is basic match indexing only.

When you want to query an attrbute, you look it up in the attributes table, then contruct the query by setting up the right condition like "WHERE attr_id=12 AND value_string='sfds'".

To speed up queries, make conditional index on dual columns, like this:

CREATE INDEX test ON eav(attr_id, value_int) WHERE value_int IS NOT NULL;

An alternative is also to have a custom db function that can index and search a column containing a JSON field. Much more work tough ...