I'm building a very basic quizz type of php/mysql/ajax game.
At this stage I'm thinking about the DB design, and specifically how to score results, I've two options in my mind. But cannot decide which one is better (being self taught. mostly by doing things, I have no idea what are best practices or what can be useful or not.)
So, I've 3 tables:
- Users -> contains: user_id, user_name, user_pass, user_score
- Quizz -> contains: quizz_id, quizz_answer, quizz_question, quizz_value
- Results -> this is the one I'm debating. Should I keep user results in a separate table and have it like: result_id, result_user_id, result_quizz_id, result_score Or simply include results for user inside users table under user_results and save it as array of answered quizz_ids
I'm debating if it is good practice to have separate results table or not. If I've it separately it means 10K users can easily create 10K*100 results rows. On the other hand array in user_results can become to contain 100 keys.
Performance wise, and taking into consideration good practices, which way is better?
Can mysql handle 10K*100 row lookups constantly on the bases of user_ID? Or is it wiser to just explode array from user_results column?