What columns for new UserStats table?

[nimmolo]

I’m working on a UserStats table and I wanted to ask you guys for your take on how the data should be organized. My idea was to have it be a pretty simple spreadsheet: one column for the user_id, and one column for each thing we score.

    create_table :user_stats do |t|
      t.integer(:user_id, default: 0, null: false)
      t.integer(:comments, default: 0, null: false)
      t.integer(:images, default: 0, null: false)
      t.integer(:location_description_authors, default: 0, null: false)
      t.integer(:location_description_editors, default: 0, null: false)
      t.integer(:locations, default: 0, null: false)
      t.integer(:locations_versions, default: 0, null: false)
      t.integer(:name_description_authors, default: 0, null: false)
      t.integer(:name_description_editors, default: 0, null: false)
      t.integer(:names, default: 0, null: false)
      t.integer(:names_versions, default: 0, null: false)
      t.integer(:namings, default: 0, null: false)
      t.integer(:observations, default: 0, null: false)
      t.integer(:sequences, default: 0, null: false)
      t.integer(:sequenced_observations, default: 0, null: false)
      t.integer(:species_list_entries, default: 0, null: false)
      t.integer(:species_lists, default: 0, null: false)
      t.integer(:votes, default: 0, null: false)

Then there are translation contributions, which we currently score by language. To keep the table simple, that would mean one column per language. It would also mean we’d need to do a migration to add a lang column every time we add a lang to the site. I don’t know how many times we’re likely to do that but it’s a consideration.

Jason suggested a completely different alternative, a “normalized” table where the columns are just user_id, field, and value . This is a very versatile option, allowing us to change what we score whenever we like, without having to do a migration. To get a user's contributions you'd do UserStats.where(user_id: 1).group(:field). To me, normalization renders the table less humanly intelligible. I prefer having a table where if you query UserStats.where(user_id: 1) you get a simple object with all the stats we score, that you can loop over. It’s not much harder to deal with a normalized table, but we don’t use them for much else, and they seem counterintuitive to me.

A third option Jason suggested was keeping the columns, but putting all the user’s translation_strings_versions in a single column to avoid future migrations to add languages to this table. In that case we might stop printing translation “scores” broken down by language, or maybe we’d do that via a live query, idk. Nathan and Joe have endorsed that idea, but i don't know, part of my curiosity about people is knowing which languages they speak. I would prefer to keep this info legible too, since we have it, and we already have a script to gather the info.

Before I make the table, though, @mo-nathan has proposed some new and (it seems to me) interesting things to score. And he says "Personally I have often wanted to totally change the way the contribution score is done. Currently it just records activity not necessarily value."

• counting consensus_namings (where the naming sticks)
• counting images with positive votes by others (maybe a multiplier on images)
• providing sequences for someone else's observation (maybe a multiplier on one of the existing columns)
• counting observations of rare or infrequently observed species (maybe a multiplier on observations)
• how many projects a person has created, or are a member of

The challenge is figuring out the things that really are good and having some mechanism to avoid having folks “game” the system.

Now is a good time to change the scoring system, before the table is created, so please sound off on these ideas.