Achievment Table

[sngapoonage]

Hi everyone,

I want to implement an "achievement" system of sorts into one of my projects. Similar to StackOverflow where if you do a certain thing or answer a certain number of questions etc you get a trophy that is linked with your user profile.

The membership section of my DB is completely empty so I'm open to ideas for how to go about this. My current thought is that I would have a user table (with all basic user info) and then an achievements table with a bit column for each achievement. But if I have say 40 achievements that means 40 columns in my table, and an amount of rows equivalent to the amount of users.

My boss is aiming in the next few years for a membership of 50,000 (unlikely where I live) so that's potentially 50000 rows and 30 columns. Is this an acceptable size for a table of little importance? I would only be selecting 1 row at a time however.

Also how would I handle achievments that require a count? For example "Answer 50 questions" and then get the achievement.

Btw, this is not a forum but I just used forums as an example for achievements.

Thanks!

[BitBlt]

That size is relatively small, but you'd have to change your table structure every time you added a new "achievement". Much better to have a normalized table structure, then you can dynamically add achievements without disrupting your existing data. Of course, adding a column in SQL Server is trivial itself, so given the small size of the table, that too isn't much of a big deal.

As far as an entire "achievement system", you could have your main achievement table be a child of the user table, and just keep track of whether or not a particular achievement was achieved; then ancillary tables to keep track of HOW it was achieved.

Have I achieved helping you achieve your goal of designing an achievement data structure? Or do I just say "achieve" too much? :D

[sngapoonage]

Thanks for your help as always.

By normalized table structure, do you mean that each achievement of each user will be a row in the user's table? So the user with 5 achievements has 5 rows in the table? What in your opinion would be the best way to do it. Now that I think about this it would be better to do that way because using my original way, if I have 40 achievements available and a user only has one, that means I still have to select 40 columns for one row (although using bits would keep it pretty small anyway I guess).

I'm not really used to dealing with big databases so numbers like 50000 scare me somewhat. Suppose everything is maxed out: We have 50000 users and every user has achieved all 40 achievements. That means the achievements table has 2 million rows? As opposed to 50000 rows using my original method. What performance issues do large numbers of rows have on select statements? I'm not so worried about inserts or updates because they can be done in the background.

I'm not sure I understand what you mean by ancillary tables keeping tack of how it was achieved. Would you be able to explain a little more?

I would say that you have achieved helping me achieve my goal of designing and achievement data structure. I think you should get an achievement for that. It's a horrible word to write. :p

[BitBlt]

So that makes us the Chief Achievers? :D

Anyway, don't be scared by the large numbers of rows (even though, in the grand scheme of things, 2 million isn't that many). We both know that the likelihood of every user getting every achievement is small. And, by having only a couple of columns, each row is relatively tiny. So, as long as the primary key is indexed, retrieval time for all the rows for one user (even 40 of them!) is negligible. You could even create a view that would retrieve all 40 (if they existed). I'll leave that as an exercise for you to think about.

So the table structure would look something like:
User (userid plus whatever else you need [name, etc.])
Achievement (achievementid plus whatever else you want [name, fulfillment criteria, etc.])
UserAchievement (userid, achievementid, plus whatever else you want [achievementdate, etc])

Now, as a potential ancillary table, you could have:
UserAchievementDetail (userid, achievementid, incrementid, plus whatever details [incrementdate, incrementdesc, etc])

So that if you wanted (for instance) to keep track of what date the user achieved reading book number 3 out of the required 5, you could keep track of that as a UserAchievementDetail. Obviously, this is just one example...use your imagination to come up with other scenarios.

Good luck!

[sngapoonage]

Ok you've convinced me I think I will use that approach. Alright so I've set up some test tables

Users
------------
ID int identity PK
Name nvarchar(80)
Email nvarchar(100)
Password binary(16)

Achievements
------------
ID int identity PK
Name nvarchar(60)
Desc nvarchar(200)
RequiredLevel int

UserAchievements
----------------
UserId int
AchievmentId int
DateReceived datetime

AchievementProgress
-------------------
UserId int
AchievementId int
Progress int
Required int
Updated datetime.

2 final questions, the first one is how to select these properly. Ideally on my page I would have ALL the achievements that are available shown. Those which have not been achieved will be sort of faded or something like that. Also those which require a certain progress (3 out of 5 etc) will display the progress. So basically I need the result set to display

Name               Desc           Progress      DateAchieved      Updated
Acheivment1     blahblahblah        1/1          22/6/2011      22/6/2011
Acheivment2     blahblahblah        5/5          22/6/2011      22/6/2011
Acheivment3     blahblahblah        3/5            NULL         22/6/2011  
Acheivment4     blahblahblah        0/5            NULL           NULL

The name and description should be from 'Achievements' table, progress and updated from 'AchievementProgress' table and DateAchieved from the 'UserAchievments' table. But I can't get the 0/5 for progress of Acheivment4 because the row would not exist, or would I pre-create the ancillary table rows?

Second question is should I use a trigger to insert the UserAchievements row, for example when an 'AchievementProgress' row is updated, check to see if the Progress has reached the required level and if it has, insert a new row? Or would it be better to run this inside my proc for updating the AchievementProgress row?

Sorry for the long post, final questions I promise

[BitBlt]

Easy...just get rid of AchievementProgress table.

The RequiredLevel is already in the Achievement table (an integer), and Progress is just an integer counter that depends on the same key as UserAchievement. Done deal. Then, if there is no progress on the Achievement for this user, no UserAchievement row.
To retrieve the data, you can cross-join User to Achievement (limit user by user id in the where clause) to get the list of all possible achievements, then left-join to UserAchievement to pick up the count of progress. All you have to do is an isnull(Progress, 0) to get a zero if there's no row.

Here's what your select statement would look like: (and I changed column names a bit...gotta look out for those reserved words!)

select users.name, 
descr, 
cast(ISNULL(progress, 0) as varchar(2)) + '/' + CAST(requiredlevel as varchar(2)) as Progress, 
datereceived, 
updated
from dbo.Users Users
cross join dbo.Achievements Ach
left join UserAchievements UsAch
on UsAch.userid = Users.id
and UsAch.AchievmentId = Ach.Id
where Users.id = 1

If it's a little fuzzy, I can post my actual table defs and test data inserts. Otherwise, I hope this helps...good luck!

[sngapoonage]

Ah so awesome worked exactly as I planned. I think I need to spend more time learning about cross joins and outer joins because I always come across these sort of problems and can never solve them. Anyway, I left my final outcome below:

I removed the AchievmentProgress table and added a progress(int) column to the UserAchievment table. Also the DateReceived column in the UserAchievments table would now get updated whenever the progress does. It would be up to my code to decide whether that date column meant when it was achieved or when the last bit of progress was added, which is trivial given the result set. I have 1 user(ID:2) and 3 achievements

Id       Name         Description         RequiredLevel
1        Ach1         blahblahblah        1
2        Ach2         blahblahblah        5
3        Ach3         blahblahblah        10

I gave the user two of the achievements one with partial progress.

UserId    AchId      Progress      DateReceived
2         1          1             22/6/2011
2         2          3             22/6/2011

I then called the query from above like this

SELECT a.[Name],a.[Description],
cast(ISNULL(ua.Progress, 0) AS varchar(2)) + '/' + CAST(a.[RequiredLevel] AS varchar(2)) AS 'Progress', ua.[DateReceived]
FROM Users AS u
CROSS JOIN Achievements AS a
LEFT JOIN UserAchievements AS ua
ON ua.UserId = u.Id
AND ua.AchievementId = a.Id
WHERE u.Id = 2

This is my result set

Name       Description      Progress          DateReceived
Ach1       blahblahblah     1/1               22/6/2011
Ach2       blahblahblah     3/5               22/6/2011
Ach3       blahblahblah     0/10              NULL

Thanks