design database table for survey form

[Priya Singh]

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?

[San Man]

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

[Priya Singh]

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

[Jeanne Boyarsky]

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.

[Jeanne Boyarsky]

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.