Sql-server – Statistical Database Optimization

database-designerdmariadbsql server

My friends and I are building a database for statistical research.
So we have a user that answers 10 questions in a row.
The questions are face-images, one is mirrored and the other one is the original, and the user should answer what the original is. From the images we know, where it lies (URL) the id and the sex.

He can participate as many times as he wants, so we need to save all his answered questions.

We want to save how many things he got right, how many he got wrong, and see if it is easier for the user to differentiate between women or men faces.

We want to store as many information as possible, without a lot being redundant.

Here is our current ER -Modell and we are not really sure if it is correct.

Statistical Database

I would appreciate any help

Best Answer

Consider having a row in the userstat table for each attempt that the user makes at the test. This means, then, that you would have to make the composite primary key of the table to be users_userid and attemptid (which can be an identity column).

I would maybe think that the questions table should be a many-to-many relationship between userstat and questions rather than between users and questions, assuming that each time a user attempts the test they may get different questions. Either way the primary key on this table needs to include more than the single foreign key, it should include both foreign keys.