Web Analytics Made Easy -
StatCounter Build vertically or horizontally? - CodingForum

Announcement

Collapse
No announcement yet.

Build vertically or horizontally?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Build vertically or horizontally?

    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)?

  • #2
    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.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

    Comment

    Working...
    X