Automatic form generation

[redbrad0]

We have contracts that our customers have to sign and mail back to us along with W9's. Depending on the type of customer they get different contracts, and some do not have to fill out any W-9's. If a form gets updated then all the customers that have been assigned to that form should have to fill out the contract again saving all the previous information for tracking reasons.

 

So basically I cannot think of the best way to design this application, here is a quick idea that I was thinking about which could take care of the version and everything else.

 

Customer_Form

CustomerForm_ID

CustomerForm_CustomerID

CustomerForm_FormID

   

Customer_FormVersion

CustomerFormVersion_ID

CustomerFormVersion_CustomerID (I know this is not needed)

CustomerFormVersion_FormID (I know this is not needed)

CustomerFormVersion_CustomerFormID

CustomerFormVersion_DateRequested

CustomerFormVersion_DateCompleted

CustomerFormVersion_CompletedByMemberID

 

Form

Form_ID

Form_Name

 

Form_Version

FormVersion_ID

FormVersion_FormID

FormVersion_VersionNumber

FormVersion_DatePublished

 

Now the problem is that each Form/Version has different number of fields and different layouts. I could create another table with the FormVersionFields that specify the field names to be filled in but then I am not sure how to build the form to be displayed without just listing the items. Does anyone have any suggestions on the best way to handle this type of situation?