Achievement / Badge System

Question

I looked at this site for an answer, but I'm still a little unsure how to plan a similar system in the structure and implementation of the database.

In PHP and MySQL, it would be clear that some achievements are earned immediately (when in the case of SO with special actions a specialized action was taken: all profile fields are filled), although I know SO updates and assign icons after a certain amount of time, With so many users and icons this will not create performance problems (in terms of scale: a large number of users and icons).

So, the database structure that I assume will be as simple as:

Badges | Badges_User | User ---------------------------------------------- bd_id | bd_id | user_id bd_name | user_id | etc bd_desc | assigned(bool) | | assigned_at | 

But, as some people claim, it would be better to have an incremental style, so a user who has 1,000,000 posts in the forum will not slow down the execution of any functions.

Will this be another table for icons, which can be incremental or just a progress field in the badges_user table above?

Thank you for reading and we will focus on the scalability of the desired system (for example, thousands of users and 20-40 icons).

EDIT: for some, you can confuse some of the confusion that I assigned as a date / time, the criteria for awarding the badge is best placed inside the prepared queries / functions for each badge, right? (better flexibility)

Answer 1

I think that the structure that you proposed (without the "assigned" field according to the comments) will work with adding an additional table, for example, "Submissions_User", containing a link to user_id and a field for increasing to calculate the arguments. Then you only need an "event listener" according to this message and indicate what you should install.

EDIT: For achievement badges, run an event listener on each view (only for the user submitting the course), and award any corresponding badge in place. For time-based icons, I did CRON work every night. Go through the full list of users once and award badges, if applicable.

Answer 2

regarding the sketch you included: get rid of the boolean column on badges_user. there is no sense: this relationship is defined in terms of the predicate "user_id" the bd_id icon has been generated when assigning_a ".

as for your general question: determine a scheme that will be relative, regardless of speed in the first place (this will save you half of the potential performance problems, possibly in exchange for various performances), index it correctly (which correctly depends on query patterns) and then, if it's slow, get the (still relational) design from what's faster. for example, you may need to pre-compute aggregates, etc.

Answer 3

I would keep a similar structure with what you have

 Badges(badge_id, badge_name, badge_desc) Users(user_id, etc) UserBadges(badge_id, user_id, date_awarded) 

And then add a tracking table depending on what you want to track, and @ what level of detail ... then you can update the table and set triggers on it to “reward” with icons

 User_Activity(user_id, posts, upvotes, downvotes, etc...) 

You can also track statistics from a different direction and run icon rewards

 Posts(post_id, user_id, upvotes, downvotes, etc...) 

---

Some other good points are made here.

Answer 4

I think this is one of the cases where your many-to-many table (Badges_User) is suitable.
But with a slight change, so unassigned icons are not saved.

I assume assigned_at is the date and / or time.
By default, it is indicated that the user has no icons.

 Badges | Badges_User | User ---------------------------------------------- bd_id | bd_id | user_id bd_name | user_id | etc bd_desc | assigned_at | | | 

This way, only marked icons are saved. The Badges_User string is only created when the user receives the badge.

respectfully
Sigersted