Databases
StackOverflow2013
Postsdbo
POWhich approach is best for storing a list of words in mysql that will later be used for statistics?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhich approach is best for storing a list of words in mysql that will later be used for statistics?
    text
    copied!<p><strong>DETAILS</strong></p> <p>I have a quiz (let’s call it quiz1). Quiz1 uses the same wordlist each time it is generated. If the user needs to, they can skip words to complete the quiz. I’d like to store those skipped words in mysql and then later perform statistics on them.</p> <p>At first I was going to store the missed words in one column as a string. Each word would be separated by a comma.</p> <pre><code>|testid | missedwords | score | userid | ************************************************************************* | quiz1 | wordlist,missed,skipped,words | 59 | 1 | | quiz2 | different,quiz,list | 65 | 1 | </code></pre> <p>The problem with this approach is that I want to show statistics at the end of each quiz about which words were most frequently missed by users who took quiz1.<br> I’m assuming that storing missed words in one column as above is inefficient for this purpose as I'd need to extract the information and then tally it -(probably tally using php- unless I stored that tallied data in a separate table).</p> <p>I then thought perhaps I need to create a separate table for the missed words The advantage of the below table is that it should be easy to tally the words from the table below.</p> <pre><code>|Instance| missed word | ***************************** | 1 | wordlist | | 1 | missed | | 1 | skipped | </code></pre> <p>Another approach I could create a table with tallys and update it each time quiz1 was taken.</p> <pre><code>Testid | wordlist| missed| skipped| otherword| ************************************************** Quiz1 | 1 | 1| 1| 0 | </code></pre> <p>The problem with this approach is that I would need a different table for each quiz, because each quiz will use different words. Also information is lost because only the tally is kept not the related data such which user missed which words.</p> <p><strong>Question</strong></p> <p>Which approach would you use? Why? Alternative approaches to this task are welcome. If you see any flaws in my logic please feel free to point them out.</p> <p><strong>EDIT</strong> Users will be able to retake the quiz as many times as they like. Their information will not be updated, instead a new instance would be created for each quiz they retook.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload