CODEWITHSUNDEEP
phpmysql
Gibbs
Gibbs

Reputation: 22956

Mysql - Table structure

I want to implement a counting system as follows

     test_id   email_id      attemptCount   status   score   subject
        1       [email protected]           1          Fail     5       C
        1       [email protected]           1          Pass     72      maths
        1       [email protected]           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      [email protected]     [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.

Upvotes: 2

Views: 73

Answers (2)

jpganz18
jpganz18

Reputation: 5858

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)

Upvotes: 2

Jaquarh
Jaquarh

Reputation: 6673

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.

Upvotes: 0

Related Questions