Storing Game Player Progress ID's in database - Comma-delimited data in MySQL vs Normalized MySQL Records (regarding server performance)

Question

I'm developing an online game which will save the player's progress to a MySQL database, and i'm trying to optimize for performance. This data would be in the form of an ID which corresponds to a specific progress achievement. These 'achievements' would consist of speaking to NPCs, initiating quests, completing quests etc, so the IDs would be added to the database in no specific order. I am currently having trouble deciding on how to store the achievement data, out of these scenarios:

1) as a comma-delimited text field (for example 32,2,53,1,3,5) on the "Player" table. Each time an achievement is triggered, the corresponding ID would be added to the end of the text field. The PHP side would perform checks using strpos.

2) stored as records inside a Player_Progress table. This could potentially mean a huge number (~100) of records per player, and I have no idea how this might affect performance.

I understand that delimited fields are frowned upon, but seeing as the data is only checked a few times, and only ever added to (not deleted), I was wondering if Normalization would be a little overkill in this situation?

Answer 1

If you were going to always read everything into a game client, persist state there (perhaps in a dictionary or hashtable), then just use the database as a flat persistence/serialization store, I could argue it doesn't matter. However this line caught me:

The PHP side would perform checks using strpos.

This sounds like you want to query on individual achievements, and using the database would most certainly be recommended here for two reasons:

1. You do not want to read the entire string and deserialize it every time you make a request to the server. State is not persisted between requests without some effort. I read the above statement to mean this game is not run with a local client, but in a browser constantly making requests to the server.
2. You can leverage the power of the database.

With a normalized database, you can simply check for a given player's achievement by the query:

SELECT 1
FROM achievements
WHERE playerId = @playerId
AND achievementId = @achievementId

And with the right indexes in place, this should be lightning-fast.

If you just had a string you'd have to first retrieve the serialized blob from the database, then either parse it into a useable data structure, or go through each character one-by-one to find the data you are looking for. This seems like a lot more overhead.

As an extra note 100 rows per player, even if you had 1,000,000 players, is not really "a lot" of rows.