Hey Luis,

Thanks for taking the time to reply. Let me see if I can better define what I am looking for.

Currently, our forms are stored in many tables with a forms table to track versioning. There are many unknowns on how we are currently storing our forms and reporting is cumbersome. I inherited a mess and a non-normalized database scheme.

The design I am looking at is:

Design # 1 (main form table and create individual tables)

CREATE TABLE tblFormMain (

"fm_id" INT IDENTITY(1,1) PRIMARY KEY,

"fm_name" NVARCHAR(20),

"fm_version" NVARCHAR(20),

"fm_areaOfStudy" NVARCHAR(20),

"fm_dateCreated" DATETIME

);

GO

CREATE TABLE tblFormSchoolApplication (

"fsa_id" INT IDENTITY(1,1) PRIMARY KEY,

"fsa_fname" NVARCHAR(20),

"fsa_mi" NVARCHAR(20),

"fsa_lname" NVARCHAR(20),

"fsa_gender" NVARCHAR(1),

"fsa_dob" DATETIME,

"fsa_student_ID" INT

"fsa_dateCreated" DATETIME,

FOREIGN KEY (fsa_student_ID)

REFERENCE tblStudentInfo(s_id)

);

GO

CREATE TABLE tblFormChangeOfAddress (

"fcoa_id" INT IDENTITY(1,1) PRIMARY KEY,

"fcoa_old_add1" NVARCHAR(20),

"fcoa_old_add2" NVARCHAR(20),

"fcoa_old_city" NVARCHAR(20),

"fcoa_old_st" NVARCHAR(1),

"fcoa_old_zip" NVARCHAR(5),

"fcoa_old_zip4" NVARCHAR(4),

"fcoa_old_county" NVARCHAR(20),

"fcoa_new_add1" NVARCHAR(20),

"fcoa_new_add2" NVARCHAR(20),

"fcoa_new_city" NVARCHAR(20),

"fcoa_new_st" NVARCHAR(1),

"fcoa_new_zip" NVARCHAR(5),

"fcoa_new_zip4" NVARCHAR(4),

"fcoa_new_county" NVARCHAR(20),

"fcoa_student_ID" INT

"fcoa_dateCreated" DATETIME,

FOREIGN KEY (fcoa_student_ID)

REFERENCE tblStudentInfo(s_id)

);

GO

-- And so on.

Design #2 (I am not sure how to extract form data from a BLOB.)

CREATE TABLE tblForms (

"f_id" INT IDENTITY(1,1) PRIMARY KEY,

"f_name" NVARCHAR(20),

"f_version" NVARCHAR(20),

"f_areaOfStudy" NVARCHAR(20),

"f_actual" VARCHAR(MAX),

"fm_dateCreated" DATETIME

);

GO

My end goal is to be able to use forms data for BI and maybe allow dynamic form creation from the UI.

Again, thank you for taking the time to look at my post.

Regards,

Steve