5

I am implementing a website where my users will gain badges upon finishing certain goals or tasks.
Badges will also have levels/colors. So for example, a user will start with a level 0 bronze badge and as they progress they will acquire a level 1 bronze badge, and after a few more levels they will acquire a level 0 silver badge and so on.

I was structuring the Database and i wasn't sure on how to connect the badges to the users.

The idea i came up with was to create 1 table for the users, 1 table for the badges, and a 3rd connecting UserID to BadgeID:

My concern here is optimization.Wouldn't accessing the table and searching the records become slow as more users join the site and gain badges?

And i read here about using a singleton in Java to run a script regularly on the server and check the users' states against the rules to upgrade the badges that meets an upper target. Is this method really scalable? or should i follow this Post instead?

TIA

Community
  • 1
  • 1
Madi D.
  • 1,980
  • 5
  • 23
  • 44
  • 2
    "Wouldn't accessing the table and searching the records become slow as more users join the site and gain badges? - not if you have the appropriate indexes. – Mitch Wheat Feb 19 '12 at 23:49
  • @MitchWheat Can you further explain your point, please? – Madi D. Feb 19 '12 at 23:54

2 Answers2

5

Wouldn't accessing the table and searching the records become slow as more users join the site and gain badges?

Not significantly, if your tables are indexed properly.

Think about it like this... If there were five times as many articles in an encyclopedia, would it take you very much longer to find an article in that encyclopedia?

Not really; because the encyclopedia is indexed alphabetically, it doesn't take much longer to find something even if the number of articles increases significantly. If it weren't indexed, though, the amount of time it would take to find things would increase exponentially as the size of the encyclopedia grew.

Given your question and the resources you linked, I feel like you are focusing to narrowly on "badges," probably causing you to miss the more general picture.

For example, if the badges are always given out sequentially, you only need to record the last badge someone got; in that case they must have gotten all the badges leading up to the last badge. If they're not incremental, using a linking table as you described may work better.

Try to describe your problem in terms related to databases or data modeling rather than terms related specifically to your problem domain ("badge"). This should help you figure out what kind of information you should be looking for and eventually lead you to the right course of action.

Dagg Nabbit
  • 75,346
  • 19
  • 113
  • 141
1

For now, you can implement it, just as you described it (1 Badge table, 1 User table, 1 relationship table).

GGG's approach to store only the last badge is a very good one, as far as a User can only have one badge and the badges are given out sequentially. However, than you wouldn't need the relationship table.

With a relationship table you will have the possibility to give out multiple badges at the same time to a user (1:n relation), e.g. Foursquare badges.

You should use InnoDB, if you are using MySQL.

If you will ran into performance problems, you can consider some kind of caching layer in front of your storage, e.g. memcached or just using a NoSQL DB just for the badge information. But I would worry about those problems, when you have to face them.

Martin Rothenberger
  • 828
  • 5
  • 6