Database architecture for the Badge system and custom criteria (MySQL / PHP)

Question

Poor-Question:

To summarize, I'm a little confused about how I will create a database that allows you to create an unlimited icon rule without requiring structural changes to previously existing user tables in the database.

Saving the name of the mark, criteria, etc. What does this table look like?

• badge_id (1)
• badge_title (10K badge)
• badge_image (10k.jpg)
• badge_criteria ([posts]> = 10000)
  ...

wound-Question:

I would like to implement a badge system in my personal projects, but I am looking for some advice on how best to do such a thing. I read some of the questions here about icon systems, but I don't see the database architecture getting a lot of attention.

Icons that are based on custom points (Hypothetical "10k Icon") look pretty straightforward. Any event that affects the reputation of users (upvotes, downvotes, answer-accepted, etc.) will refer to the method of reviewing the new reputation of users and potentially award an icon.

This system sounds pretty straightforward, but that it looks like a database for the administrator who wants to create countless amounts of badges with little effort in the future - some of them can be based on different criteria, and not just the user's reputation.

User reputation is probably the value inside the user record itself. But ideally, would you like not to add new fields to the user table when creating new icons? For example, the "Edited 100 Entries" icon - you would not create a new column "entries_edited" in the Users table, would you? And then increase that after each edited entry ...

Any clues?

Stackoverflow Archive:

• Best way to save icon criteria (not duplicate)
• Icon System Design: Where to Launch Business Logic?

---

Note. I DO NOT ask how to associate icons with users. I do not ask how to award badges (this will be done programmatically)

Answer 1

Given that skipping criteria can be arbitrarily complex, I don’t think you can save it in a database table broken down into “simple” data elements. Trying to write a "rule engine" that can handle arbitrarily complex criteria will take you to the next path, basically rewriting all the tools that you have in your programming language.

If you know in advance that the icons are limited to only certain fields (i.e., the icons are based only on reputation or the number of corrections or something else), you can save them in a simple table, for example:

 ReputationBadgeCriteria BadgeId BadgeName MinReputation 

Alternatively, you can use some kind of DSL to write your own “rules”, but in the end you should also create a parser to analyze the rules when reading them, as well as to execute these rules. Depending on the complexity that you want to use in DSL, this may not be a trivial task. This is similar to the path you ask in your question, having a Criteria column (supposedly plain text) that has something like "[Reputation]> 1000" or "[Posts]> 5". You still need to disassemble and follow these rules, and the difficulty of writing something for this depends on how complicated these rules are.

I would recommend you read these WTF Daily articles for information on why this approach leads to pain.

Answer 2

Depending on how much you want to go with it, your circuitry can become quite complex. It seems to me that the basic elements you need to track are as follows:

 Badges awarded Points earned 

Pretty simple so far, but you want to be able to dynamically create new badges and new categories of points. Signs of awards will depend on the points of earnings in one or more point categories, which may amount to a certain amount. Therefore, you need to track the relationship between the categories of points (and the points gained) and the icons:

 Point categories Badge categories 

Thus, the key will be a table of your user points, which will refer to the categories of points that refer to the icons. Users receive points in a certain category, which will help attract points to one or more icons.

 badges: badge_id badge_name required_points .... point_categories: point_id category_name weighting (optional) ... point_groups: badge_id point_id weighting (optional) ... user_points: user_id point_id points ... user_badges: user_id badge_id points_earned badge_awarded (yes/no) ... 

Your admin interface will allow someone to create a new badge and choose which categories of points are needed to get this badge (point_groups). Whenever a user earns points (user_points), you update the user_points table and then determine which icons these points could contribute to (point_groups). Then you recompile the badge points affected by the points received and update the user_badges table with point_earned. Then check the points_earned field in user_badges for the required_points in the icon table.

You can become much more attractive by assigning different weights to different categories of points or even different weights for point categories for certain icons. But this setting allows you to create and manage an unlimited number of icons and categories of points without changing the structure of the tables.

If this is not at all what you are looking for, I think I should at least get a voice or two to type a lot.

Answer 3

You will track your unique users in one table and unique icons in another, and then create a cross-reference table to link them.

A user can have many icons, and an icon can have many users.

 create table users ( id int, name varchar ) create table badges ( id int, badge_name varchar ) create table user_badges_xref ( user_id int, badge_id int ) 

Statistics that can affect whether a user receives an icon are tracked as part of the site’s administration. therefore, something like the accepted answer will be in a circuit that links questions and answers. to display the response and the owner of the response, it would be relevant to the user table and triggers that will check the icon conditions whenever a change is made.

I do not ask how to award badges. I ask how to store criteria in a database.

So, you want to save the logical operation necessary to determine whether the icon was entered in the field in any place?

I think I agree with another poster that the criteria should be part of the business logic. This logic can be on the application side or inside the trigger. I think this is a matter of style.

If you were really married to the idea of ​​storing criteria in a field, I would save it as parameterized SQL and run it dynamically.

So this will be in your criteria field:

 select "Badge Earned" from all_posts where user_id = @user_id having count(*) > 10000 

Answer 4

I would not create an increment for the edit icon. I think you should have a job running in the background and count () the number of the message to be edited for members who do not yet have an edit sign. When you see that the counter is above the range you want, you add a record to the database, which indicates that the user has an icon.

I think about the same for the other icons. Try to limit the number of letters and not directly record the account of information about the icon in the user table. Use a table that will contain information about the icon and bind it to the user table.

Answer 5

I apologize for being short.

To implement such a system, I could create a table that stores the names of stored procedures or current queries that will be used to determine whether a particular user received an icon.

 badge_criteria badge_key int badge_criteria varchar(max) 

You can retrieve and execute queries for icons that the user has not earned from your middle level, but you would not need to make any changes to the code or structure to add new icons in the future.

Answer 6

I approach it as follows: create a table to store all the icons and put a single link in the column function that runs to see if the icon is checked. Thus, the table remains simple, and the logic for determining the icons can be stored in code where it is best suited.

Using this method, icon requirements can also be linked together to form more complex dependencies. For example, to receive this icon, the user must receive three separate special icons with a specific timeframe.

Answer 7

This will be almost impossible to do in the database - the rewarding of the badges should be done in your business logic in the application. Thus, you have all the existing data that you need (editing, visiting, reputation, etc.), and you can handle it at your discretion.

Update:

If by criteria you mean rules that determine whether an icon is assigned and how it is assigned, then this is not something that should be stored in the database. It would be almost impossible to verify and maintain.

If you mean, for example, storing the "number of corrections", then there is no way to circumvent a table or stored procedure change to include this data if you need it.