WW3 database question

[pstaabp]

We've talked a bit about having both an attempts table and an answer table. I don't think I ever figured out the details here and am starting to work on that in the database.

attempts

This table will store basically any attempt from a student. If I understand correctly, I'm guessing this will include entering an answer in a box that may automatically send to the server instead of just on a "submit". I think this is a bit like the current past_answer table.

Do we mainly just store the answer or the score/value as well?

answer

This is the actually answer submitted by the student for a given problem. For this, we'll store

• current score (value)
• answer string(s) as submitted.

If this is all, then this seems like we can store this in the user_problem table.

I feel like I'm definitely missing some things.

[dlglin]

The terminology is going to get confusing here. What I think of as an attempt may better be described as a version of a problem. Basically my thought was to replace the user_problem table with something that had a separate record for each version of the problem that was presented to the student.

Here's my current thought:
The user_problem table has a separate record for each attempt. There is a field for answer, and an additional boolean field for "submitted". If "submitted" is false, then the answer field contains the most recent saved answer (or is null if no answer has been saved). Once the answer is submitted, the boolean is changed to true, and if allowed a new record is created for the next attempt.

@drdrew42 suggested having an additional place to store every intermediate answer that was saved along the way. I think of this as more of a log, and would behave similarly to the current past answers table.

[drdrew42]

I would suggest that any row in user_problem refers to a problem that has been assigned to that user, with the potential for including a version counter. This means that a user_problem row refers to only a specific version of an assigned problem, and may have multiple allowed attempts. Those attempts belong in another table.

What belongs with the user_problem (beyond the fundamentals: problem path, problem seed, incorrect attempts count, etc) is the 'state' of the problem. By this I mean to say the most recent contents of the answer fields, regardless of submission.

What I'm not saying is that this should be a comprehensive log of edits - it should not be. Rather, this 'state' field is held simply to provide users the opportunity to "pick up right where they left off" independently of whether or not they 'submitted' their answers. When a user modifies an answer field's contents, a db update should be triggered and the 'state' field updated to reflect the modification. (This update should definitely be throttled by the frontend, so as to avoid a flurry of db updates.)

Moving on from the user_problem table, there ought to be a submitted_answers (or 'attempts') table. This table contains a row for each submission on any problem for any user. This table should contain a jsonb field that holds the contents of the form-data supplied by the request (allowing for 100% accurate reproduction of the attempt). I don't know that it needs to hold much more than that beyond a reference to the user_problem ID to which the attempt applies. Perhaps the resultant status ('score') of the submission is relevant here and should be stored in the submitted_answers table as well.