Merit badges and counselors for Boy Scout site

How do you make a functional merit badge counseling section on a troop website? Boy Scout troops have some degree of turnover in both adults and scouts. Plus, merit badge counselors need to be registered with the district. And, ideally, they should have received training to be merit badge counselors as well.

How to provide a place on the troop website that accommodates all these requirements? What is an easy, maintainable way to link users on the site, in the role of either parent or leader, with merit badges, so that we could construct views of all the merit badges and show who are qualified counselors for that merit badge. And not just anyone can edit who's who.

Another consideration: because merit badges are used in so many places throughout the troop website (to tag events, meetings, merit badge counselors), I thought I could/should use a taxonomy for the merit badges.

Approach #1
I found a profile_taxonomy module that enabled me to add a profile field whose choices were taxonomy terms. I also found a profile_checkbox module that enabled me to select more than one merit badge per person. It was important that users not have access to set their own merit badges, and the profile_taxonomy module gave me control so I could disallow that access. Perfect, right?

Only problem is, the way the data is stored in the database makes it so that Views doesn't work right. Looking in the database, the merit badges show up as a comma-delimited list all in one field. That's going to cause problems, as you'll see.

So then I created a User View called Merit Badge Counselors, that was supposed to list all the merit badges and which users are their counselors. Problem is that the way Views constructs the queries, it works only for users that are counselors for only one merit badge. I honestly don't know SQL well enough to properly frame the query… If I did, maybe I could create a module that did some operation on the SQL query before it fires...

Approach #2 - a taxonomy-based query. Nope. Only works on nodes, not profiles.

Approach #3 - Create a Merit Badge content type. That'd be handy anyway, because we could use it to describe merit badges and attach users to the merit badges as user references.

So I created a quick PHP script that bulk-created all the merit badges and associated them with user reference CCK fields. What remains to be seen is whether others are able to edit who the merit badge counselors are for each merit badge. Stay tuned...