What is best: 1 table per record or 1 table with all records linked with foreign keys?

Question

I have an application that lets users create different forms (surveys) and then fill them. (so its a substitute for paper).

Here's the current model i'm using in the app:

 Table 1)
+-------------------------+
|      SURVEYS TABLE      |   
+----+------+-------------+
| ID | name | description |  
+----+------+-------------+

 Table 2)   
+-----------------------------------+
|       $[name_of_the_survey]       |
+----+-------+------+-------+-------+
| ID | field | type | value | items |
+----+-------+------+-------+-------+


 Table 3)
+--------------------------------------+
|    $[name_of_the_survey] _records    |
+----+---------------------------------+
| ID | columns specific to each survey |
+----+---------------------------------+

so basically when a user creates a survey, the programs inserts a record in Surveys Table and then creates 2 tables:

table (2) for the fields of the form table (3) for the records that will be stores, in which the columns correspond to table (2) rows.

It works but has some limitations. For instance, when you which to add a field to table (2), it has to read table (3) contents, save it to a virtual table, drop previous table (3) and create a new one. This can be a performance issue when the table(3) has a lot of records.

So my question is... Is there a better database design?

Answer 1

Using a separate table for each survey nearly invalidates the use of a database. You might as well just store the results in files.

You do, however, need three tables: Survey Definition, Survey Questions, and Survey Answers. It may look something like this...

Surveys:
ID; name; description

Questions:
ID; text; surveyID

Answers:
ID; answer; questionID

You could add complexity from there to handle enumerated answers...

Surveys:
ID; name; description

Questions:
ID; text; surveyID

Choices:
ID; choice; questionID

Answers:
ID; choiceID

You use the relationships between each table to aggregate to the next highest level, allowing you to get results from any question, survey, or any other attributes for any model you choose to add without trying to abstract away the source for your select statements. This also allows you to aggregate answers per user or surveying organization later on after adding them to your schema. If each survey has its own table structure, aggregating data across surveys becomes hugely impractical as your application grows.

Answer 2

You might try taking a look at http://en.wikipedia.org/wiki/Database_normalization#Normal_forms

The above is quite a formal way of improving DBs in general, and some of the steps are relevant to your DB. I think it's a bit confusing with all the ID fields. Do you really need them for each one? Are survey names not unique?

You've implied that the survey data fields are quite unique. Personally I would sort put each survey into a file, and just give it a standard format. It isn't a bad idea if the tendency is to read an entire survey at once. I'd only use a DB if I needed to sort or pick and choose bits of data.