php - Mysql - Table structure -

i want implement counting system follows

     test_id   email_id      attemptcount   status   score   subject         1       a@a1.com           1          fail     5       c         1       a@a1.com           1          pass     72      maths         1       b@a1.com           1          pass     62      c 

just see, (test_id,email_id, attempt_count, subject) forms unique row.

i want insert row each test attempt. attemptcount must incremented 1 same user, same test id, same subject.

how can do?

1) can last attempt count key (test id, email id, subject) table

       test_id    email_id    subject   last_attempt  

2) can use triggers auto increment attempt count. [i read way in 1 of posts]

3) have change table structure. have separate table each subject. primary key composition test_id, email_id, attempt_count

is there other way achieve this? 1 best way do?

i feel have rethink table structure.

edit

is practice have multiple values in single cell follows?

     test_id  email_id   overallstatus              overallscore  subject         1      a@a1.com     [pass,fail,fail,pass]   [10,2,3,10]     maths 

then there 1 row combination (test_id, email_id, subject). don't care attemptcount in case.

my aim want store attempt results tests of users.

i use 3 tables.

test , subject , test_result

id have test this

test_id email_id //other columns such date, description, etc 

table subject

subject_id (auto_increment) test_id attempts //you can add date , latest status or score 

this historical table, in case want report:

test_result

test_result_id(auto increment) test_id subject status score 

so, when make insert on test_result can update subject +1 on attempt (from table subject), , if want update latest result on table (to avoid making query on table long, can too)