Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Database design for random form data?

Author  Topic  fetlock Starting Member 8 Posts Posted - 2004-03-30 : 16:05:47 I'm working on a design for collecting data from a variety of forms. Anyone have experience with something along these lines, or can anyone spot areas for improvement / big problems with the design? I want to receive and save data from any number of forms, with any number of form elements, and pull the data out for reporting. The coding part I'm comfortable with. If this were just one form, or forms that I knew the structure of ahead of time, I'd just build tables based on their structure... but it's not, so I'm thinking of approaching it by type and length of data: Table FormSetup (Data about the form on first submit, collected from hidden fields) formid int PK formName vc(50) formEmailTo vc(50) formSubject vc(50) formFrom vc(50) formRedirect vc(50) Table FormSubmit (Data about each submission, which form it belongs to) formsubmitid int Pk formid int Fk submitdate datetime submitersIP vc(20) Table FormDataVCShort (Form data that's a short vc) shortid int PK formfieldid int FK content vc(50) Table FormDataVCMed (Form data that's a short vc) medid int PK formfieldid int FK content vc(500) Table FormDataVCLong (Form data that's a short vc) longid int PK formfieldid int FK content vc(2000) Table FormDataFields (Data about each field in the form) formfieldid int PK formid int FK fieldName vc(150) fieldLength int (length of first or avg data for this field...needed?) fieldtable vc(30) (table name where this data is...needed? iffy?) fieldOrder int (what order the field is listed on the form) ...and a disclaimer, table names, field names, lenghts, etc are all just rough draft... off top of my head, just trying to get the idea across... and those short-med-long tables could be divided out further... maybe... It's not super efficient, but, it might be better than using FSO and writing a txt or csv file and dumping the data there... and better than doing nothing and hearing users beg for a way to collect the data they receive from these emails...