heramb22

I have an application that will show users a series of forms and I need to be able to store this data in a mysql database. The data must be associated with the user and the account the user came into the system from. All pieces of collected data must be searchable, and it would be a plus if they were stored in appropriate column types (datetime, varchar, int, ...) rather than all being thrown into text fields with fulltext indexes. Just to give you an idea of the amount of data that will be stored: Expected users: 5,000 (to start) - 200,000 (within 2 years) Unique forms: 50 - 200 (most users will only fill out 20-25) Average fields per form: 10 Here are a few of the ideas along with the pros and cons of each. 1.) New table for each form. pros - organization cons - data duplication if same field stored on multiple forms, MANY tables 2.) Store all data in one table pros - no data duplication, easier to prepopulate duplicated fields, fewer joins cons - wasted space as each row may not have a value for every column, MANY rows 3.) Come up with some kind of datapoint mapping scheme pros - ??? cons - still haven't quite figured out how it would work Any ideas, suggestions, or even terms I should Google to find something that would help are greatly appreciated.