0

Working on the DB design of a Badge System which will work synchronously(request/response) for variant criteria for more than 1000+ badges I am wondering how the badges will be checked when a user does a request.

A first DB Schema I am thinking is:

Badge
id     name
1      Check-in 10 Beers, of X Manufacturer the last 30 days
2      Check-in 5 Organic Beers from England
3      Comment on 5 different check-ins

Rules
id      key
1       count_of
2       manufacturer_of
3       last_x_days
4       comment_x_diff_checkins
5       from_country_x

BadgeRules
badge_id    rule_id    value
1           1          10 (check-in 10 beers)
1           2          122 (manufacturer_id)
1           3          30 (days)

The think is that the User makes a check-in and the system should check the different variants for different badges.

As an example need to check on the above DB data that the User made 10 check-ins of Manufacturer with ID 122 the last 30 days. Obviously makes no sense to check 1000+ badges for each request but somehow can be checked only the Badges that the User can win for that check-in.

I was thinking to save for user's state in a different db table but I dont think that this can work for all the rules like last_x_days

UserState
id   user_id   key          value
1    1         checkins     8
2    1         comments     12

P.S. I have seen the Untappd app which has thousands of Badges and it assign them on users request.

I would be grateful for a proposal.

apo
  • 53
  • 6
  • Do you want to manage rules using mysql? Rather you can use php by having an array with index namely beers, manufacture_id etc. – Ankit Jindal Jun 30 '20 at 04:55
  • @AnkitJindal yes I want to manage rules, not necessarily with mysql but the result will be saved in mysql. The thing is that need to be fast because the badges will be more 1000+ – apo Jul 01 '20 at 06:04

0 Answers0