Users and Achievements

[svcghost]

Hey guys,

What would the best / most efficient way to incorporate an achievement system be for a MySQL database?

I have a users table with users in it.
Should I make a whole new table for achievements called "achievements"?

How would I most efficiently link this table to the users table? A user needs to earn the achievement so it's either unearned or earned. By default, it's unearned. There are over 100 achievements.

Just need ideas. Thanks so much guys.

[tesuji]

Hello

Assuming that

1st, a user may have earned 0, 1 or more achievements

and

2nd, an achievement may have been earned by 0, 1 or more users

then a many-to-many relationship exists between user and achievement. So your data model consists of three tables:

user(userID, name, etc)
achievement(achievementID, description, etc)

and

a so-called linking table which defines the many-to-many relationship:

userachievement(userID, achievementID, date, remark, etc)

Implementing this little data model on mysql (which is not a good database system for beginners) require that all primary keys and foreign keys be correctly defined. This is a must!

You should follow these suggestions:

create table user(
  userID int not null auto_increment, 
  name varchar(100) not null,
  primary key (userID)
);

create table achievement(
  achievementID int not null auto_increment, 
  description varchar(100) not null,
  primary key (achievementID)
);

create table userachievement(
  achievementID int not null auto_increment, 
  userID int not null auto_increment, 
  date timestamp,
  remark varchar(100),
  primary key (userID, achievementID),
  foreign key (userID) references user(userID),
  foreign key (achievementID) references achievement(achievementID),
);

-- above code NOT tested, so there could be typos!

Important: There is no (contra-productive) auto-incremented ID column in the many-to-many linking table! So never add such stuff to that sort of tables.

Don't omit any of the above key-definitions. They are all essential. If primary key (userID, achievementID) is not unique, what rarely happened sometimes, we should discuss how to solve this wee problem.

It is not necessary that the primary keys of user and achievement are auto-incremented if their values are given from elsewhere, for example given debtor number for users.

You know Joe Celko, no? So you may google him and what he wrote about ID-xxx in his famous book "SQL for smarties".

-- tesu

[svcghost]

Thank you very much tesu. Very helpful reply.
I'm going to try this model out and run a few tests.

There shouldn't be any reason for stumbling upon a non-unique primary key in the userachievement table if the primary key is (userID, achievementID). So I don't think that'll be a problem.

I'll let you know how it works for me.

Thanks!

Edit:
Question: Let's say there are 50 achievements. So this means every time a new account is created, I must create 50 new records in the userachievement table (1 for each achievement for that new user)?

[tesuji]

>>> Question: Let's say there are 50 achievements. So this means every time a new account is created, I must create 50 new records in the userachievement table (1 for each achievement for that new user)?

Oh no! You may have stored thousands users and thousands achievements in both your tables users and achievement. An entry in table userachievement must only be made if the concerned user has indeed earned that specific achievement.

Exception from that rule: If every new created user has already earned all those 50 achievements, yes, then you have to do this tedious job :)

-- tesu

I also assumed that a user cannot earn same achievement twice, therefore no duplicate primary keys.

[svcghost]

Excellent!

Good point. Why store it if it's unearned? Waste of space.

Thanks again tesu!
I will keep you updated.