MySQL - Which is more efficient?

[Redgie]

Figured this was the most appropriate place to put this, apologies if it isn't.

I'm adding an achievements system to a game of mine. Just wondering if it's more efficient to:

A: Have a field for every achievement on every profile
B: Have an achievements table with PlayerID, Achievement that is inserted into when they get the achievement.

Bearing in mind there will be roughly 50-100 achievements.

Essentially, is it more efficient to add the achievements to EVERY userfile, or add a seperate table? As not everybody is likely to do all achievements...

Thanks!

 

[GravyMonster]

Definately separate out achievements in to their own table. This will save you headaches later should you need to amend/create achievements. To do this using the first method, you'd need to amend your schema.

I would create a new table containing the keys of the account and the achievements theyr've earned, something like this:

Account
---------------------------
ID
Name
...

Achievement
---------------------------
ID
Name
Description
...

Account_Achievement
---------------------------
ID
AccountID
AchievementID

 

[FabienO]

What spunkey said.

Key is to not store large bits of text in tables u wish to reference often.

User_id
1233

Achievement_id
1, 2, 3, 7


Then when you need to display a description of the achievements of a user, grab the numbers and use a global array.

global $achievement_descriptions;

echo $achievement_descriptions[$achievement_id]['name'];

 

[Redgie]

I doubt this is relevant, but the game is written in P (Easier to refer to it as C as it's similar), and isn't directly web based, though does have web based features.

The achievements themselves will be hard coded into the source code as they are unlikely to change very often. But I will add an achievements table to record people getting their achievements.

Thanks a lot guys, appreciate it