I plan to build a web application which would have dynamic forms with custom fields. he can create one module, web form having some custom fields.So these fields can be created by customer itself through web application.
To make this enable i believe I should have some tables to store meta data details of these custom fields and their properties. few other tables to store their values.
Expected Output in Application:
Employee Number : 12001
Name : Denny Cherry
Designation : Senior Database Administrator / Architect
Age : 26
Status : Active
Table to store Field Properties:cust.fields
fldno fldname datatype length field_control module
1 Employee No NVARCHAR 20 Text_box Employee
2 Name NVARCHAR 50 Text_box Employee
3 Designation NVARCHAR 20 PickList Employee
4 Status NVARCHAR 20 PickList Employee
Table to store Field Values:cust.FieldValues
RowId FieldId value Pr_Index SecIndex
101 1 12001 12001 NULL
101 2 Denny Cherry 12001 NULL
101 5 26 12001 NULL
I could transpose the fields into columns and display one employee record in a single row. But it would take huge time by creating more number of fields and storing their values.
I could successfully make user to create custom fields and forms on their own through this method but I guess I may be wrong in terms of data base model/architecture.
How would you recommend to store the fields meta data and their values?
Thanks in Advance.
Software/Hardware used: SQL Server 2005, Java Server Pages