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?

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?

how to make database in form?

how to make form?

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.