BBGA Badge-scheme Management System

Logo displaying the words “The Boys’ Brigade & Girls’ Association. > The adventure begins here.” To the right: an golden ship’s anchor with the words “Sure & Steadfast” engraved upon it with a red cross behind it with the anchors rope dangling behind that, and, in blue, capital letter Bs to the immediate left and right.
The logo of the Boys’ Brigade and Girls’ Association

This project, undertaken around April 2012, was inspired by my local BBGA company. I designed, developed and iterated over a web-application which was used regularly until 2017 when an official web-application that aimed to solve the same problems, and had greater integrations was made available.

The Problem

For about 3 years in a row badges had not been awarded to members due to the badge-scheme being changed. Much to the disappointment of its members as the badges were symbol of achievement they could display with pride.

The primary issue was that the badge-schemes were too complex for volunteer leaders alone to learn how they should work and how they should be implemented. This was further compounded by there being a different badge-scheme (with a different set of rules) for each section (age-range of members).

As a way to remedy this issue I intended to encode the rules of each badge-scheme into a web application so that the leaders could see who had achieved which badges, who was close to reaching each badge and what would each member need to complete the requirements for as yet unachieved badges.

Understanding the Badge-schemes

I read each of the badge-scheme specifications to work out the ways in which they were similar so as to make sure the underlying data structure upon which the badge-eligibility was decided was the same.

Logos of badge schemes, top-to-bottom: Anchors, Juniors, Discover, Challenge Plus
Badge-schemes by section (age): Anchors—Anchor section (4–8), Juniors—Junior section (8–11), Discover—Company section (11–15), Challenge Plus—Senior section (15–18).

In each badge-scheme an activity is given a category and participation in said activity awards a number of points related to the duration of the activity. The exact categories were different across the badge-schemes as was the point accumulation rate.

A member was eligible to be awarded a badge provided they had accumulated points in one or more categories above a set threshold, and for some badges a sufficient attendance level or previously been awarded a specific badge. In the case of the Challenge Plus badge-scheme, a member has to not only accumulate a sufficient number of points across 3 different categories but also complete a personal development challenge set by a section leader.

Outside of the four section-associated badge-schemes there was the Discovery, President’s, Duke of Edinburgh and Queen’s badges (the special badges) which had there own requirements; the years of service badges, awarded based on the years part of the BBGA; and the rank badges, awarded on a discretionary basis.

Technologies Used

At the time of undertaking this project I had easy access to a server running the LAMP stack, thus it made sense to make use of it. My use of an SQL database required a relational decomposition of the problem space and, although I was unfamiliar with the term at the time, using PHP in an Object-oriented manner would require a certain level of ORM.

As I wanted to get this application in the hands of those using it as fast as possible so I could iterate upon the project, I chose to use a Stylus port of the Bootstrap CSS framework (Stylus being my preferred CSS pre-processor at the time).

For the small areas that absolutely required client-side scripting, I used JavaScript without any frameworks and instead doing most dynamic aspects server-side, again maximising iterations was a considerable factor in this decision.

Technical Design

Since I was intending to use a relational database, I used relational decomposition to understand the problem space.

Diagram showing relationships between Activities and Sessions, Sessions and Members, Sessions and Leaders, Users and either Leaders or Members, Members and Challenges, Members and Badge-work and Members and Special Badge Requirements.
ERD showing the result of initial relational decomposition of the problem-space.

One challenging part of the problem space was that each badge-scheme had different yet similar categories for activities. To solve this, I created names for sub-categories that sufficiently described the captured the sorts of activities that should belong in one category in one badge-scheme but a different category in another.

Additionally, the BBGA company that I was developing this for segregated the members into different sessions, by section. This meant that a session needed to be restricted to which sections of members should be expected to attend.

Further developing this idea, it made sense that not all activities would be appropriate for all age-groups, and thus activities could be restricted to certain sections. This would also have the benefit of a user having the ability to filter a list of activities to only those suitable for the session being planned.

Calculating Point Accumulation

After that all that remained was designing the queries that would calculate the number of points a section of members had accumulated over a period of time based on their attendance and (assumed) participation in the activities, by category.

The SQL query used to construct a table displaying members’ progression and achievement under the Discover Badge-scheme.
SELECT `Users`.`ID`,
       `Users`.`Surname`,
       `Users`.`Forename`,
       `Users`.`DateOfBirth`,
       FLOOR( DATEDIFF( CURDATE(), `Users`.`JoinDate` ) / 365.25 )
         AS `Service`,
       `Activities`.`Category`,
       SUM( `Activities`.`Duration` * `Inspection`.`Attendance` ) / 60
         AS `Points`,
       `Badgework`.`Discover Compass`,
       `Badgework`.`Discover Community`,
       `Badgework`.`Discover Recreation`,
       `Badgework`.`Discover Skills`
FROM `Users`
JOIN `Inspection` ON `Users`.`ID` = `Inspection`.`User ID`
JOIN `Activities_Sessions`
  ON `Inspection`.`Session ID` = `Activities_Sessions`.`Session ID`
LEFT JOIN `Sessions` ON `Inspection`.`Session ID` = `Sessions`.`ID`
JOIN `Activities` ON `Activities_Sessions`.`Activity ID` = `Activities`.`ID`
JOIN `Badgework` ON `Users`.`ID` = `Badgework`.`User ID`
WHERE `Users`.`Level` = 'M'
  AND CASE (
        FLOOR( PERIOD_DIFF(
          {$lastSept},
          DATE_FORMAT( `DateOfBirth`, '%Y%m' )
        ) / 12 )
      )
        WHEN  4 THEN 'Anchor Section'
              ⋮ /* 4–8 Anchor Section */
        WHEN  8 THEN 'Junior Section'
              ⋮ /* 8–10 Junior Section */
        WHEN 11 THEN 'Company Section'
              ⋮ /* 11–14 Company Section */
        WHEN 15 THEN 'Senior Section'
              ⋮ /* 15–18 Senior Section */
        ELSE 'Err'
      END = 'Company Section'
  AND `Sessions`.`Date` > {$lastEnrolment}
GROUP BY `Users`.`ID`,
         `Activities`.`Category`
ORDER BY `Users`.`DateOfBirth` ASC

Additional Use-cases and Features

Beyond the initial requirements, I went on to add more features that were requested, these were:

A series of form fields with labels matching the names of badges to the left of a dynamically generated image of an armband with the chosen badges pinned upon it.
The Armband Generator: a page allowing members to see how their badges should be arranged.

Limitations

Due to the project always being at a prototype stage (albeit high-fidelity) with the requirements evolving rapidly as the system was in use.

Many initially envisioned features were unimplemented, such as having multiple user accounts have access to viewing different amounts of information. For example, a member being only able to see their own progression, a parent or guardian able to see their children’s progressions, section leaders able to see their section’s progressions and company captain access to all functionality.

Additionally, some aspects remained at implementations that relied upon assumptions that did not necessarily hold, and further feature were built that relied on their functionality.

A table with the columns Name, Years of Service, Date of Birth, Points (sub-columns: Christian Faith, and Community, Recreation and Skills each with 4 sub-sub-columns), and Badges (sub-columns: Compass, Community, Recreation and Skills). The Name and Date of Birth columns have data redacted. The Points columns are filled with values of 0 highlighted red, values in range 0-1 highlighted yellow and values greater than 1 highlighted green. The Badges columns contain the occasional icon of an award on rows where the corresponding category has all sub-categories green.
The report of progression in the Discover badge-scheme (personal information has been redacted).

One such example is that the designed system would award badge-scheme points to a member provided that they were not absent during a session in which a badge-scheme-contributing activity took place for which their section and section restrictions matched. This assumed that the member in question participated in the activity, whereas in reality they may have been unable to or refused to participate or were disruptive to other members participation.

In retrospect, I’d have solved this problem by freezing the member–session–activity relationship in a separate database table, upon the conclusion of each session. This would be the source of truth from which the badge-scheme reports were derived and leaders would be able to edit this table at a more nuanced level.

Evaluation

This project was an overall success as it brought back the badge-scheme aspect to the activities of the BBGA and was used for 4 years.

In late 2017, a similar system commissioned by The Boys’ Brigade England, was rolled out. This system included the additional features of inter-company sessions and activities, integration with the store (for the purchasing of badges and uniforms, etc.) and made membership paperwork much easier. As such the company I had been working with transferred to this system, with the understanding of the badge-schemes they had learnt from using the system I had developed.