Take the 2-minute tour ×
Game Development Stack Exchange is a question and answer site for professional and independent game developers. It's 100% free, no registration required.

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:

  1. Users -> contains: user_id, user_name, user_pass, user_score
  2. Quizz -> contains: quizz_id, quizz_answer, quizz_question, quizz_value
  3. 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?

share|improve this question
add comment

closed as off topic by bummzack, Nate, Jimmy Shelter, Byte56, Trevor Powell Mar 16 '13 at 0:33

Questions on Game Development Stack Exchange are expected to relate to game development within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

2 Answers

up vote 5 down vote accepted

Let my explain, why you should definitely add your results as a separate relation-table and why having a string of IDs inside the user table is "wrong":

First of all, try to look at the database as a separate sub-system. The sub-system should be self-contained. So if you have a table Users and Quizz and there's a relation between the two (eg. user have multiple or one results per quiz), then this should be reflected in your database design.

Why? Most importantly, it allows you to work with the Database outside of your application code. If you leave it up to your application to make the relation between Users and Quizz, then you're severely limiting the usefulness of the database itself. You won't be able to use any other tool (eg. directly querying your database for these relations) without re-implementing or re-applying that logic you used in your application.

Having your Results stored in the Users table can also limit performance if you have to implement DB locking (which might be necessary with lots of concurrent users).

Also be aware that you're about to perform premature optimization. Databases are already heavily optimized and can handle thousands and millions of rows. Without identifying the database as a bottle-neck, there's no reason to deviate from good practice. I highly doubt that querying the user table, splitting the IDs to an array and then querying the Quizz table with these IDs individually is going to be any faster than what the DB would do for you with some simple JOIN statements.

If you're interested in the topic, you might want to read up on Database normalization. There's another candidate in your design that might be off, which is the user_score column in your Users table. I suspect that this is a running total of all result scores? If yes, you should get that with a query from your results instead:

SELECT SUM(result_score) FROM Results WHERE result_user_id = <yourUserID>
share|improve this answer
    
Perfect, very informative, thank you for taking time to write this up. I've a better idea now how to make DB :) –  Sandro Dzneladze Mar 7 '13 at 6:18
add comment

If I remember my database design 101; long tables are always preferable to wide tables.

Essentially, a million rows isn't really anything even close to a big deal for a relational database; even 10 million shouldn't really make MySQL sweat.

This also comes back to design: the results shouldn't be on the user table, because they are not part of the user, but a property of the user.

share|improve this answer
    
+1. I wonder why this is even a question. Storing results as comma separated values in the user table is just plain wrong. Then you could just as well skip the database entirely and use a text-file.. –  bummzack Mar 6 '13 at 9:14
    
@bummzack: I'm thinking he is thinking separate rows. :P –  Williham Totland Mar 6 '13 at 9:39
1  
"Or simply include results for user inside users table under user_results and save it as array of answered quizz_ids" and "Or is it wiser to just explode array from user_results column?" made me think otherwise. Either way is a bad idea though. –  bummzack Mar 6 '13 at 9:45
    
Thank you for explaining it to me. I had a hunch having a separate table for results was the best way to go... but I wanted to know why. As for @bummzack it might not be obvious to hobby programmers... just plain wrong is not an explanation either. –  Sandro Dzneladze Mar 6 '13 at 11:28
    
@SandroDzneladze I'm sorry, that was indeed a bit out of line and not really helpful. I tried to explain it in a separate answer. –  bummzack Mar 6 '13 at 15:26
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.