0

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?

2
Contributors
4
Replies
5
Views
7 Years
Discussion Span
Last Post by kpeeroo
0

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?

how to made database?

0

Thanks for reply.

No, its not the database form but the design of the tables. Anyways, i have found a solution to this by creating 3 tables:

Questions
------------
QID
Question

Answers
----------
AID
Answer

StudentReply
----------------
StudentReplyID
QID
AID
StudentID

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.