How to allow flexible html form fields yet easy to run sql reports?

I am building a website that allows applicants to submit application forms. The fields for the application form need to be flexible to allow changes.

The traditional method is to map every single form field to a database column but this is pretty limited in terms of growth as new fields are introduced as system evolves. As new columns are introduced, existing database rows would have null values or some kind of "default" value due to "missing data".

However if I make the fields a key/value driven approach it will be very hard to do reporting later on.

So I am looking for some suggestions/recommendations if someone has done similar implementations. Thanks.

Example 1 (field -> column):

app form may have the following fields:

• first name
• last name

a开发者_开发百科nd the related database table shall look as below:

• first_name nvarchar(255)
• last_name nvarchar(255)

Example 2 (key/value pairs):

• first_name (key column), john (value column), textbox (type)
• last_name (key column), smith (value column), textbox (type)

I found some examples like polldaddy.com wufoo.com which allow dynamic web/html form generation but I think in my case they are kind of useless due to reporting requirements. And I think their implementation would be similar to my "example 2".

Updated:

I found this project (mvc dynamic forms) and I believe the concepts are similar to what I need to achieve. I will take a deep look at the project.

For running of the application (see OLTP) you'll want to use the key/value pair approach you mentioned, it's the only sensible way to achieve what you need in terms of flexibility (and have a system which is maintainable).

A good approach to get around the reporting problem is to have separate database schemas for the transactional (OLTP) and reporting (OLAP) bits. Differnet schema doesn't mean different physcial database - although it might make sense to separate them at some point.

You'd then have some sort of ETL process that migrated data between the two (from the OLTP source to the destination OLAP tables).

If you keep the OLTP, OLAP and ETL logic all in the same place it will be easier to manage and preserve nice clean separation. Alternatively you could build the ETL logic into your application - it really just depends how you've architected the rest of the solution (have you abstracted out the data access completely or not) and what your drivers are (is this an in-house tool, cloud-based or will it be a system people deploy onto their own kit.

The beauty of the separate OLTP / OLAP set-up is that both are geared towards doing their respective jobs well - without impacting on the other.