Hi there

Imagine a database that is only accessed by a server application. This
application provides the ability for admin users to create templates
for forms. When a template is created, other users of the application
can then submit instances of a form, which is then stored in the
database.

The form templates are used by the application to generate GUI front
ends to enter data. The form data collected is transmitted to external
systems for processing.

This must be a common problem so how would any of you model this in a
relational database?

There are two approaches that come to my mind:
Solution 1. Header table and Value Pairs

A table stores the header info for each form template.
e.g.
CREATE TABLE FORM(
FormID NUMBER PRIMARY KEY,
FormName VARCHAR2(40) NOT NULL,
CreatedBy VARCHAR2(40) NOT NULL,
CreateDate DATE NOT NULL)

Another table is used to store the field names

CREATE TABLE FORMFIELD(
FormFieldID NUMBER PRIMARY KEY,
FormID NUMBER CONSTRAINT formfield_fk REFERENCES FORM(FormID),
FieldName VARCHAR2(40) NOT NULL,
FieldType VARCHAR2(40) NOT NULL,
FieldLength VARCHAR2(40) NOT NULL,
IsRequired NUMBER NOT NULL,
CanBeNULL NUMBER NOT NULL)

Then, instance tables are used to store the instances of forms
submitted by users.

CREATE TABLE FORMINSTANCE(
FormInstanceID Number PRIMARY KEY,
FormID NUMBER CONSTRAINT forminstance_fk REFERENCES FORM(FormID),
CreatedBy VARCHAR2(40) NOT NULL,
CreateDate DATE NOT NULL)

CREATE TABLE FORMFIELDINSTANCE(
FormFieldInstanceID NUMBER PRIMARY KEY,
FormInstanceID NUMBER CONSTRAINT formfieldinstance_fk REFERENCES
FORMINSTANCE(FormInstanceID),
FormFieldID NUMBER CONSTRAINT formfieldinstance2_fk REFERENCES
FORMFIELD(FormFieldID),
FieldData VARHCHAR2(100) NULL)

The FieldData field is used to store actual data captured when users
fill in a form. This data then has to be CAST to the appropriate type.

Now this looks like a workable solution to me but I don't like the way
data has to be stored as a VARCHAR and CAST back to something else.

Solution 2. Header table and Dynamic Table Creation

As before,
A table stores the header info for each form template.
e.g.
CREATE TABLE FORM(
FormID NUMBER PRIMARY KEY,
FormName VARCHAR2(40) NOT NULL,
CreatedBy VARCHAR2(40) NOT NULL,
CreateDate DATE NOT NULL)

However this time, the application creates a new table for each form
template which is then used to store the form data
e.g.
CREATE TABLE AUTOAPPLICATIONFORM1(
FormInstanceID NUMBER PRIMARY KEY,
FormID NUMBER CONSTRAINT autoapplicationform1_fk REFERENCES
FORM(FormID),
ApplicantFirstName VARCHAR2(40),
ApplicantLastName VARCHAR2(40),
ApplicationDate DATE,
etc...

In this solution we could end up with many tables, yet we are using
the storage facilities of the RDBMS better. Also we are allowing the
schema to dynamically change.

From a practical and theoretical point of view, which of these
approaches is better?
Have any of you seen this problem before?
Is there another way I haven't thought of?

Thanks!

Karen