Database Schema Question

[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.

[otuatail]

whould help if you sent us a typical example of the data entered and the user who entered it. Is there a log on form to start with.

Desmond.

 

[Mchl]

Look into Entity-Attribute-Value data model: http://en.wikipedia.org/wiki/Entity-attribute-value_model

 

Here are some examples in MySQL: http://explainextended.com/2010/04/02/multiple-attributes-in-a-eav-table-group-by-vs-not-exists/