Proper Way To Store User Defined Data In Sql

I want to build an online form builder much like wufoo that allows the users to create and publish their own web forms. Each submission should be saved to a data base where the use

Solution 1:

store user defined data in SQL

I think you are looking for the Entity–attribute–value database model in which:

The basic idea is to store attributes, and their corresponding values, as rows in a single table.

Typically the table has at least three columns: entity, attribute, and value. Though if there is only a single relevant entity, e.g. a table for application configuration or option settings, the entity column can be excluded.

See this pages as a start:

• Using Database Metadata and its Semantics to Generate Automatic and Dynamic Web Entry Forms (pdf)

• Planning and Implementing a Metadata-Driven Digital Repository (pdf)

I retagged your question with entity-attribute-value tag, in which you can browse a lot of threads that relate to your case.

Solution 2:

As Mahmoud Gamal writes, The model you describe is "Entity/Attribute/Value"; as Borys writes, there are many known problems with this model.

As an alternative, you might consider storing the form entries in a "document" - e.g. XML or JSON - within a relational model.

For instance, you might have a table along the lines of:

FORM_SUBMISSION
--------------------
Submission_ID (pk)
Client_ID (fk to clients table)
Submission_date
SubmissionDocument

I'm using "client" to represent the users who create the form; to retrieve all submissions for a given client, you use a where clause on client_id.

This model makes it harder to run SQL queries against the form submission (though that becomes hard with EAV too when going beyond very simple queries), but it dramatically simplifies the persistence solution.