I have these entities into my database
1-Users
2-Questions
3-Answers
Now I already knows that some of the questions are of single valued and some of them are multi values and the users has to give the answer for these questions (check the attached form for reference with name questions.png)
In that diagram There are some questions and their target and total the target comes from the Question Target table (see the Diagram QuestionTarget for reference) The Target from from the Question target table and the answer goes into the answer table see the answer.png for reference .
The Question is that I am stroing the individual question target on row by row mean question 01 target would be saved on its own row in table and question2 target would be saved in its own row so i have 16 questions so 16 rows inserted for target ,but while saving the answer of these question target I am saving one row for all question target Total value like that
Table:
AnswerID Question01Target Question01Total Question2Target Question2Total --------so on
so when making the query to find the sum of Question01Total vs Question01Target these two are comming from different tables Question01Total from Answer table and Question01Target from QuestionTarget table I have no way to join these two tables because in Question Target table I am saving the each target on its own row and in Answer table i am saving all the answer in one row like Question01Target,Question01Total,Question02Target,Qustion02Total.....
I also have the question 01(for question one id) same question02 (for question02id) in answer table checck the table should i make all(question01,question02,question03.....question16) as forign key with Question_ID column in Question target table.....
or this database design for answer table is not correct please i need quick response thanks in advance for all supporters.