design database table for survey form

HI,

This is a very common practice but I am looking for a robust and tested solution for designing db table for a survey form.

has anyone worked on such project before.. i have an option to create 20 columns (for 10 Q n 10 Answers) in the table but that doesnt look so effiicient ..

and i think another option as keepin a master table of Questions and then a transaction table of questions n answers but in this case inserting and retrievin records will take more time..

any suggestions?

Could u please explain ur question in detail?Please don't be in a hurry

Sure. I have to design a database table for survey form which has 10 Q and corresponding answers (as radio button options + comment text)

keeping into mind the no of times code would need db connection for insert time and retrieval time, i have 2 options.

1) i create a table of 30 columns; 10 for Q, 10 for A, 10 for comment text but this will take too much of code rewriting . This is good for one time db connection required for insert data.

2) i create a transaction table of 3 columns; 1 for Qid, 1 for Ans, 1 for Anstext.
another master table to store Qid, Qtext. where Qid acts as forign key in transaction table. This requires less coding (speaking in terms of prepare statement in java) . But to insert a record one by one for each Q, it requires db connection 10 times which can make the application slow.

I hope i could clear the situation to some extent.
please let me know if it made ne sense.

Thanks

Priya,
Option #2 is significantly better. Think about what would happen if you decided to have 20 questions in your survey. Option #1 would involve rewriting everything. Option #2 would just be more iterations of the loop.

it requires db connection 10 times

Not really. You can use the batchUpdate() method and do it in one server roundtrip.

I'm going to move this to our JDBC forum (near the top of the forum list) since it isn't specific to Oracle and JDBC gets more traffic.