I have an idea of the design but I want to know if it wont impact performance. I have a student form consisting of Qu like:
- Do you have internet access
- Relationship
- Do you want to pay by cheque, credit card + other options
- etc
So I wanted to create 2 tables as thus:
tblDataTypes
-id
-value
tblRelations
-ID
-parentID
-childID
Examples:
tblDataTypes:
ID Value
1 Relationship
2 Internet Access
3 Payment
4 Married
5 Single
6 Divorced
7 Yes
8 No
9 Cheques
10 Credit Card
etc
tblRelations:
ID parentID childID
1 1 4
2 1 5
3 1 6
4 2 7
5 2 8
6 3 9
7 3 10
etc
I did it this way so that even if more questions are added after, the table does not have to be re-built, instead having the parent child relationship, i can easily extend the form data.
The problem am having is now, if i want to relate these tables to the students, how do i do that. Would i
1) do a table consisting of say 10 columns for 10 different questions with their id's as foreign:
ex:
tblsurvey
ID studentID internetAccessID relationshipID paymentID ...
1 1 4 2 6
The foreign keys above are obtained by quering the 2 tables above and the primary key of tblRelations inserted in tblSurvey
2) each question relates to studentID
ex:
tblSurvey
ID studentID answerID
1 1 4
2 1 2
3 1 6
etc
Option 2) will have many rows and I think that would impact on performance for number of searches that have to be made while option 1) has a problem of expansion. What if the form needs to be updated and more questions added, so option 1) table's design would have to be re-done.
Any suggestions?