Database design - Your suggestions

[w11z]

Hello everyone! I must create a online survey and I am wondering what would be the best way to approach this. The survey is simple i.e :

Question 1. Evaluate your knowledge of ...
a) MS Word
b) MS Excel
c) etc.

The scale is from Excellent to Poor. Radio buttons (5) are being used to grade the knowledge of. I have designed 2 other surveys. The first one looked like this.

CREATE TABLE Answer(
no_client number(6),
no_question varchar2(4), // received i.e Q1A, Q1B, Q1C, Q2A...
theAnswer number(1) // received 1 to 5
)

But this design was not flexible and hard to create stats from. So for the second survey I have tried:

CREATE TABLE Answer(
no_client number(6),
Q1A number(1),
Q1B number(1),
Q1C number(1),
Q2A number(1),
etc...
)

But this design is very long (if the survey has 100 questions or more...) and very long to compile the stats.

My question is : what would be the best way to design this survey... Any suggestion would be appreciated.

Thanks

 

[carp]

Your first design looks quite good and should support statistics quite well. It is similar to tables we use quite often.