Is there a table that holds the badge name, its description, and retired status?

Question

SO has badges that are awarded to users for completing some tasks. I was playing around with SO Data Explorer and noticed there is no table for badge definition itself.

So for example if I want to figure out list of users who received a 'Moderation Badge', I need to scrape a list from Badges page then build the query.

Same way if I need to build a page showing list of badges, with explanation of badge and who received it first, again I need to scrape the badges page and build it manually.

Question

Is there a table that holds the badge name, its description, retired status, And if there is one, would it be possible to include it in data explorer, please?

Or in other words, where the data for Badges specifically description and category of each badge coming from. Stackoverflow is an example and would be nice if I could get data for badge description and category for all SE sites.

Note that:

• This is not related to TagBased badges, they are already included in data explorer.
• Is there any documentation about the data model behind the reputation system? does not answer my question.

Answer 1

There is no such table in the SEDE schema.

if you look at the result for my gold badges in this query

select *
from badges
where userid = 578411
and tagbased = 0
and class = 1 -- gold

you'll find

Id	UserId	Name	Date	Class	TagBased
7419206	578411	Copy Editor	2013-09-04 22:19:13	1	False
7912782	578411	Marshal	2013-10-26 23:52:04	1	False
5096365	578411	Electorate	2012-12-22 15:31:48	1	False

For most other tables the primary key Id is usable in one way or the other in a route url on the site (comments, posts, users, reviews, suggestededits)

I don't know the actual schema used in production but I imagine it is along these lines:

 + ------------+         +-------------+
 | UserBadge   |         | BadgeTypes  |
 |-------------|         |-------------|
 | Id          |\        | Id          |
 | UserId      |--o----- | Name        |
 | BadgeTypeId |/        | Description | 
 | Date        |         | Class       |
 +-------------+         | TagBased    |
                         | Created     |
                         | Retired     |
                         +-------------+

The SEDE export "Badges" is probably a view over these tables. It is specifically a shame that the BadgeTypes.Id isn't included because without it you can't create a link for the specific user to the specific badge. For my Marshal badge the url would be:

https://stackoverflow.com/help/badges/1298/marshal?userid=578411

but we don't have that pesky 1298 so no magic links from SEDE are possible.

I don't know what the rationale back then was to omit the BadgeTypes table. I can see how it helps for some minor interesting queries like lead-time to first badge after introduction. And it would help building a magic link. I'm not sure that is enough motivation to justify the effort needed for adding this table to SEDE.