web based quizz game database design, how do I store results? [closed]

Question

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?

Answer 1

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>

Answer 2

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.