Unanswered: Which apprach is better to to introduce an ID field or combine other fields?
I have the following problems when i am designing my database for an online exam system:-
1. I have a question table and answer table; where each question contains many answers and each answer have one question.
Now i have two approaches for designing the answer table, either by having:-
For example, in the first case, ANSWER_DESCRIPTION may be "GROUP BY clause is used to sort records returned by the SELECT statement" (which is wrong - ORDER BY clause is to be used here - but it might be an option in your "answers" list). Having that string as part of a primary key is, well, not the right approach. Answers should have their IDs (numerics, I suppose), and they should be used as primary keys.
I think ANSWER table has answers of multiple choice questions.
Go for first approach with this change.
In the case Table should have PK on (Question_no,Answer_id).
So it will be easy to validate the answers. By selecting description there may be issue of performance in case of large question database.