Should an application ever be allowed to change a schema?

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 Received on Sat Apr 17 2004 - 13:16:10 CEST