I'm in process of developing a Facebook application. It's kind of a games portal like Mindjolt plus a "non-flash Facebook game" (more such games soon).
So I store some data in a MySQL database for each user as you would expect.
I need some tips on how to store data so that;
- the application works fast,
- scales fine in the future with thousands (or much more hopefully) of users
- and it's easy to retrieve/sort user data whenever I need a new type of data in the application (new games, new statistics about existing games etc.).
So I have a very basic understanding about database normalization but I have some table structures that I know against this principle.
Hmm, let me shorten this post and give an example so that you can comment on a real case.
I have a word game in the app. that users make words from letters they have.
I store all words made, in a table like this:
Columns: id, userid, word, wordscore
So when I need to display a higscores table, I need to select all words a user made from the table, get the sum of wordscores, and then sort users in the order of the scores they made in total.
Now, here is what I did instead even if this is against normalization (is it?):
I'm keep the wordsMade table as it is, so that I might be able to re-collect proper data if things go wrong in the future.
But I add another column to allUsers table (primary table to store username etc.) which holds the total score for each user and updated whenever a user made a new word.
This way I just sort the allUsers table ordered by the totalScore field. Because it seems to me that it's much faster (is it?) especially if I have thousands of users and many many more made words than that.
As you can see I'm not experienced with huge-scale databases and how to handle them. I often feel "if I do this now, it'll cause performance issues when I have much more data." and go against normalization.
Well that's enough for now I guess. Feel free to reply with specific solutions to the example I've described above or some general principles I should consider, or maybe with some resources I need to read about structuring a database.
Thanks in advance.