Search code examples
sqldatabase-normalization

questionaire time spend mysql

Say I have a table where I would store questions. Now I would like to track how much time people on average spend per question and how many came up with the right solution.

  1. Would I store the time spend per question in the table_questions itself or in a different one.
  2. Would I store the answered right in the table_questions or in a seperate one, maybe even with time spend.

The reason why I am hesitating is two fold. First off I rather not want the user to be able to perform update queries on my questions. But seperating the time spend and "answered good" in a different table seems weird to me because they are inherent to the question?

Does anyone with normalization talent (unlike me) know what would be a good approach?

Solution

  • My suggestion:

    Don't name tables TABLE_QUESTIONS or TABLE_USERS or anything similar, unless you have a good reason, and I cannot think of one at the moment. Just call them QUESTIONS and USERS.

    If you actually have a USERS table, and you care who answers correctly (I cannot tell based on the wording of the question), then I think you should also have a USER_QUESTIONS table. The tables might look like this:

    QUESTIONS
---------
Question_Id    
Question_Descr

USERS
-----
User_Id        
User_Name

USER_QUESTIONS
--------------
Question_Id
User_Id
Answer
Grade
StartTime
EndTime

    Then questions (and only questions) go in their own table, and users (and only users) go in their own table. But when a user answers a question, it goes in the mixed table.

    You have a many-to-many relationship between users and questions, and creating an intermediate table like this is the normal way of resolving that.