Badges DB Design

Also see:

There are two separate databases:

  1. App Database which is PostGreSQL
  2. BadgeOS / WordPress database which is MySQL (version 5.7 which does not support CONNECT BY)

We're learning what will work to store in one DB versus the other, but generally:

  • Badge data goes in BadgeOS and leverages existing code that we expect to be "supported" with on-going and "backward compatible" releases. However, with the release of version 3.x of BadgeOS, we've found that there are code-breaking changes to the database structure — even though those changes were favorable.
  • Application-specific records are landing in the PostGreSQL DB.
  • Achievements and Badges are recorded within BadgeOS
  • Views can be used to pull records from the following interesting tables:
    • wp_posts
    • wp_postmeta
    • wp_badgeos_achievements

Diagram - Badge Progress

This shows BadgeProgress — the classes which support incomplete Badges.

Sub-Achievements have a post_name that hyphenates the Title and look more like a Badge in that they are explicitly named as requirements for a given badge.

Earlier Examples

These may no longer be used, but this hasn't been checked.

Meta per Badge

SELECT pm.* FROM `wp_postmeta` pm
  JOIN badge_brief b
    on b.post_id = pm.post_id

Steps per Badge


SELECT p.post_title as badge_name,
       child.post_title as step_name
  FROM wp_posts p
  LEFT JOIN wp_p2p p2p
    ON p2p.p2p_to = p.ID
  LEFT JOIN wp_posts child
    ON child.ID = p2p.p2p_from
 WHERE p.post_type = 'seekers'

Recursive Queries

Not clear that I want to pursue this.

select  id,
from    (select * from tablename
         order by parent, id) tablename,
        (select @pv := '1') initialisation
where   find_in_set(parent, @pv) > 0
and     @pv := concat(@pv, ',', id)

Dirty Data

Coming across some problem records when running this query:

SELECT p.ID as step_post_id,
       if (p.post_type = 'step', p.post_title, null) as step_name, 
       if (p.post_type = 'constituent-step', p.post_title, null) as constituend_step_name,
       parent.ID as parent_id,
       parent.post_title as parent_name
  FROM wp_posts p
  LEFT JOIN wp_p2p p2p
    ON p2p.p2p_from = p.ID
  LEFT JOIN wp_posts parent
    ON parent.ID = p2p.p2p_to
 WHERE (p.post_type = 'constituent-step' 
    OR p.post_type = 'step')
   AND > 3000
  • There are "Auto Draft" records which do not have a parent.
  • There are records with no title that list unused records as parents.

From 2017


  • Use PostGRESQL as platform since Clue Ride is a Geo-spatial application, but would be nice to have ability to support MySQL as well. Currently installed on Mensa
  • Familiar with Hibernate, but not a requirement
  • Credentials provided as part of an authenticated session; all requests will be specific to that User ID with exception of admin accounts which have raised credentials (separate API?)
  • Module supports REST API with JSON payload.
  • Java-based service layer
  • Want to explore DAO layers

Use Cases

UC diagram here

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License