Build vertically or horizontally?

[topramen78]

I'm new to this site and know a little bit of MySQL from a syntax standpoint, but am still fairly new when it comes to structure.

Need some help here. I'm building a survey which has approximately 30 questions. I'm expecting about 1,000 responses to this survey.

One way to build the tables is to build a user table ... then build a responses table where it looks like this.

Users
userid
name

Responses
questionid
userid
response

Only problem with this method is that I'm looking at a table that is 30,000 rows long. Would it better to create do it this way?

Responses
userid
question1
question2
question3
...
question20

This table would be about 1,000 rows long (and easier for me to extract the data into an Excel sheet for reporting purposes since I won't need code to join all the data), but it would about 30 columns wide.

What is the best way to build a table? Build it horizontally (i.e 30,000 rows by 4 columns) or vertically (1,000 rows by 30 columns)?

[raf]

The first design is the standard design for this sort of apps. Because you then can have infnite number of questions (or a non-fixed number of questions) without needing to change the table-structure. Specially if you want to hang up other variables to that table (like a registration datetime, or the score for that question etc)
It is then also easier to select and present the recorded answers, without needing to wory if they filled in all questions
a table with 30k records is no problem at all.


on the other hand. If the number of questions is fixed (so no questiosn need to be added), and if each user needs to fill them in al, then there is no real reason to not simply use a flatfile-like approach and create a new column for each question. But this is realy only an option if you don't gonna change the number of questions + if you don't want to link the questions to other tables3.