High scores w/ MySQL - is this good/bad approach?

[ironchefmoto]

I'm finishing up a Flash quiz that's powered by PHP/MySQL, and I'm trying to figure out the best method for storing the high scores in the database. Here's an overview of what I had in mind:


User finishes quiz and has a total points score stored
User fills in 3-letter initials (arcade style) and submits their score
Score goes into quizScores table (id, points_regular, points_bonus, points_total, initials, user_ip, user_browser, user_timestamp)
After updating quizScores, check user score against the lowest score in quiz_HighScores
Delete the lowest score in quiz_HighScores table and insert the user's high score
Return the top 10 or top 20 high scores to the Flash interface


Is there a better way to do this?

Keep in mind that I am not storing unique usernames as part of a registration or login system. This is strictly anonymous high scores. I'm storing ALL the scores in case I need to do a running tally of all scores. However, that's going to be slower than keeping a separate small batch of high scores in it's own table, right?

Finally, is it better to have the quiz_HighScores table structure the same as the quiz_Scores structure? Or should I use foreign keys tied to the quiz_Scores table?

If this is a bad approach, I'd like to know. Any suggestions are very appreciated.

IronChefMorimoto

[ironchefmoto]

I'd also like to, in lieu of the user getting a top 10 or 20 high score, to at least let the user know where they ranked in the quiz_Scores table. Say, 435th out of 1000 quiz scores for a specific quiz.

If, after I perform the INSERT on quiz_Scores and then perform a SELECT on the quiz_HighScores table to find out their score isn't a top 10 or 20, how do I go back and retrieve their relatively anonymous/non-unique score ranking from quiz_Scores?

I ran into this same sort of problem with an e-Card generator -- I only stored the messages and not "unique" user identifiers. I ended up generating an MD5 hash in the message table to assist in finding an anonymous but unique row.

I guess I could return that to Flash BEFORE performing the SELECT on the quiz_HighScores table. Then, if their score isn't a high score, Flash knows the MD5 hash to look for in the quiz_Scores table of anonymous scores and can then pick out the rank with some ordering/counting code.

Thoughts on this as well?

Thanks,
IronChefMorimoto

[majicassassin]

1) I wouldn't use foreign keys linking the two tables together, just because the HI-score table is so small linking it like that would just slow it down since it'd have to query the other...
2) The separate high-score table is nice for speed, but how many people are you really going to have taking this quiz? If its just a single quiz, a hi-score table is probably overkill in terms of time spent coding. If you have a lot of quizzes, you would want to do the hi-score table then. Don't forget that mySQL was designed to hold hundres of thousands of entries.
3) To figure out the users position inte score table, the easiest way (for you) is to use a mySQL query like
SELECT * FROM quizScores where score > users_score
This will return all the rows greater than the users current score, at which point there's PHP command to count the rows. This is probably magnitudes better in terms of time and bandwidth spent that it would take to pass all of the relevant data to flash and let it find itself in the list.


Here is my PHP code that I run to retrieve hi-scores out of my database, it grabs the top 10 scores from 'gameScore', then out puts the players score and name in XML:

mysql_connect($host,$user,$password) or die("Can not connect to mysql");
@mysql_select_db($database) or die( "Unable to select database");

$query="select * from `gameScore` order by `score` DESC LIMIT 0, 10";
$result=mysql_query($query);
//echo $query;
echo "<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>\n";
echo "<scores>\n";

while($row = mysql_fetch_assoc($result)){
echo "<playerScore score=\"".$row["score"]."\" player=\"".$row['player']."\"/>\n";
}
echo "</scores>";

[ironchefmoto]

OK -- I did a really bad job of describing this earlier.

Here's what the quiz does:

Admin user interface will allow creation of unlimited # of Flash-based quiz instances
For each quiz instance, a unique ID -- the quiz configuration ID
Need to associate, in a quizScores table, all anonymous user scores -- each score tied to a given quiz instance ID
At the very least, the stored scores need to be called up to show either the top 10 or top 20 scores for a given quiz instance
Would like to keep the score storage anonymous -- limited to the 3 arcade-like initials that we require the user to enter at the end of a given quiz instance; makes it hard to go back and do a SELECT on a specific score later, though


That said, I backed off the two table setup I was looking at earlier. This is what I originally thought that the two tables would accomplish:


For every score recorded on a quiz instance, I would always INSERT each game's score in the quizScores table; it's gonna get big very fast if this tool is popular
If the user's score was higher than the lowest score stored in the top 10/20 quizHighScores table, I would DELETE the last row and INSERT the new, higher score into quizHighScores; only 10-20 rows per quiz instance ever, possibly speeding up SELECT statements on the quizHighScores table (???)


Right now -- I've avoided the 2nd table option for lack of ideas on my part. This is what I'm doing for now with my code:

// insert the new scores
$scoreQuery = mysql_query("
INSERT INTO cxn_QuizScores
(
quiz_config_id,
points_regular,
points_bonus,
points_total,
initials,
user_ip_address,
user_browser
)
VALUES
(
'$scoreConfigID',
'$scoreRegular',
'$scoreBonus',
('$scoreRegular' + '$scoreBonus'),
'$scoreUserInitials',
INET_ATON('$scoreUserIP'),
'$scoreUserBrowser'
)
");

// return the top 10 scores after the INSERT query
$scoreQuery = mysql_query("
SELECT points_total, initials
FROM cxn_quizScores
WHERE quiz_config_id = '$quiz_config_id'
ORDER BY points_total DESC
LIMIT 0, 10
");

For now, this will do what I need at the most basic level. I worry that this will take a performance hit as this quiz tool is used more and more. And, right now, I can't really tell a user that didn't make the top 10 or 20 by telling them their specific position in a given quiz instance's scores (i.e. 475th out of 2053).

Any other suggestions are very welcome.

Thanks folks!

IronChefMorimoto

[majicassassin]

Yeah, deleting would definitely keep the table manageable. I would argue as long as its a semi-decent server even if this tool becomes popular if you only have 10 or so instances per quiz you'll still be okay.

As far as giving a player not in the top 20 their position, there really is no easy solution. You could always lie and just keep the number of people who've taken the quiz and from that extrapolate an approximate position based on their score, the highest score, and the lowest score. Other than that, there is no good way to keep your table from becoming unruly.

I'd personally question whether or not you're going about the hi-score thing in a correct way though. This is because if it is a quiz, you can probably expect many people to make easy quizzes and lots of people to be tied for first. At that point you have a problem anyways because showing the top 10 most likely won't even be informative anyways.

Ah, one more idea. Since these are quizzes, there are a definite number of possible final scores (1 right, 2 right, etc etc). You could just have a table that stores how many people have gotten each of these. For example, a 3 question quiz could have a table like this:
Num_correct Num_people
0 10
1 5
2 6
3 2

In this way, you could defnitely say that someone who gets 1 right is in a 7-way tie for 9th place.

This would probably keep a constant record of the quizzes for minimal cost (I assume each quiz will probably be short and have something like 10-15 questions), at least keeping the size of your database constant regardless of the number of users.

-maji