I want to implement a counting system as follows
test_id email_id attemptCount status score subject
1 firstname.lastname@example.org 1 Fail 5 C
1 email@example.com 1 Pass 72 maths
1 firstname.lastname@example.org 1 Pass 62 C
(test_id,email_id, attempt_count, subject) forms unique row.
I want to insert a row for each test attempt. So attemptCount must be incremented by one for the
same user, same test id, same subject.
How Can I do?
1) I can get the last attempt count for the key (test id, email id, subject) from another table
test_id email_id subject last_attempt
2) I can use triggers to auto increment attempt count. [I just read this way in one of the SO posts]
3) I have to change my table structure. So I will have separate table for my each subject. So primary key composition will be
test_id, email_id, attempt_count
Is there any other way to achieve this? Which one is the best way to do?
I feel like I have to rethink about my table structure.
Is it good practice to have multiple values in single cell as follows?
test_id email_id overallStatus overallScore subject
1 email@example.com [pass,fail,fail,pass] [10,2,3,10] maths
Then there will be only one row for the combination
(test_id, email_id, subject). I don't care about attemptCount in this case.
My aim is that I want to store all the attempt results for all the tests of all the users.