MVC: Create user_achievements table and UserAchievement model
Now we have the "master" achievements table, we need somewhere to link them to users
We probably need to store:
- user (no index)
- achievement (no index)
- created_by (user)
- revoked (bool, default false, not null)
- revoked_by (user)
- revoked_at (timestamp)
Rather than accepting the default indexes, it might be best to create
- user, revoked
- achievement, revoked
If we consider revocation to be the only way an achievement can be revoked, we could drop the revoked_at column, but it seems a little flakey to me
I'm also a little unsure about storing created_by and revoked_by- it seems like a good idea for audit purposes, but i'm not sure if they should be stored as events so they can be included in activity feeds
I think we should AVOID assuming that user + achievement create a unique key as I envisage achievements which can be awarded multiple times in the future
Edited by Lee Tickett