Gops AB February 2016

Mysql - Table structure

I want to implement a counting system as 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 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.

EDIT

Is it good practice to have multiple values in single cell as follows?

     test_id  email_id   overallStatus              overallScore  subject
        1      a@a1.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.

Answers


Kyle E4K February 2016

Your methods/functions will have to be created in some sort of programming method, you won't be able to achieve all of this only passing in data to your Database.

You're correct about using an auto_increment on your test_id however, your primary key would be your test_id.

There is nothing wrong with that.


jpganz18 February 2016

I would personally use 3 tables.

test , subject and test_result

Id have test like this

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

table subject

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

This will be like an historical table, in case you want a report:

test_result

test_result_id(auto increment)
test_id
subject
status
score

So, when you make an insert on test_result you can update subject with a +1 on attempt (from table subject), and if you want to update the latest result on that table (to avoid making a query on this table which could be very long, you can do it too)

Post Status

Asked in February 2016
Viewed 3,976 times
Voted 10
Answered 2 times

Search




Leave an answer