Even though the question is specifically about the database design, I will give a solution for the tracking/awarding logic as well to establish more accurate context for the db design.
I would store the achievements progress separately from the already awarded achievements for cleaner tracking and discovery.
The whole logic is event based and has multiple layers of event handling. This gives you TONS of flexibility on how you track your data and also gives you a pretty good mechanism to track history. Basically, you can look at it as a form of logging.
Of course, your system design and contracts are highly dependent on the information you're gonna be tracking and its complexity. A simple progress field may not suffice for each case(you might want to track something more complex, not a simple number between X and Y). There is also the case of tracking data which updates quite frequently(as distance travelled in games, for example). You didn't give any context on the topic of your achievement system so we're gonna stick with a generic solution. It's just a couple of things that you should take a note about as it will affect the design.
Okay, so, let's start from the top and track the entire flow for a tracked piece of data and its eventual achievement progress. Let's say we're tracking consecutive days of user login and we're gonna award him with an achievement when he reaches [10].
Note that everything below is just a pseudo-code.
So, let's say today is [8th of July, 2017]. For now, our User entity looks like this:
User: {
id: 7;
trackingData: {
lastLogin: 7 of July, 2017 (should be full DateTime object, but using this for brevity),
consecutiveDays: 9
},
achievementProgress: [
{
achievementID: 10,
progress: 9
}
],
achievements: []
}
And our achievements collection contains the following entity:
Achievement: {
id: 10,
name: '10 Consecutive Days',
rewardValue: 10
}
The user tries to login(or visit the site). The application handler takes note of that and after handling the login logic fires an event of type ACTION:
ACTION_EVENT = {
type: ACTION,
name: USER_LOGIN,
payload: {
userID: 7,
date: 8 of July, 2017 (should be full DateTime object, but using this for brevity)
}
}
We have an ActionHandler which listens for events of type ACTION:
ActionHandler.handleEvent(actionEvent) {
subscribersMap = Map<eventName, handlers>;
subscribersMap[actionEvent.name].forEach(subscriber => subscriber.execute(actionEvent.payload));
}
subscribersMap gives us a collection of handlers that should respond to each specific action(this should resolve to USER_LOGIN for us). In our case we can have 1 or 2 that concern themselves with updating the user tracking information of lastLogin and consecutiveDays tracking properties in the user entity. The handlers in our case will update the tracking information and fire new events further down the line.
Once again, for brevity, we're gonna incorporate both into one:
updateLoginHandler: function(payload) {
user = db.getUser(payload.userID);
let eventType;
let eventValue;
if (date - user.trackingData.lastLogin > 1 day) {
user.trackingData = 1;
eventType = 'PROGRESS_RESET';
eventValue = 1;
}
else {
const newValue = user.trackingData.consecutiveDays + 1;
user.trackingData.consecutiveDays = newValue;
eventType = 'PROGRESS_INCREASE';
eventValue = newValue;
}
user.trackingData.lastLogin = payload.date;
/* DISPATCH NEW EVENT OF TYPE ACHIEVEMENT_PROGRESS */
AchievementProgressHandler.dispatch({
type: ACHIEVEMENT_PROGRESS
name: eventType,
payload: {
userID: payload.userID,
achievmentID: 10,
value: eventValue
}
});
}
Here, PROGRESS_RESET have the same contract as the PROGRESS_INCREASE but have a different semantic meaning and I would keep them separate for history/tracking purposes. If you wish, you can combine them into a single PROGRESS_UPDATE event.
Basically, we update the tracked fields that are dependent on the lastLogin date and fire a new ACHIEVEMENT_PROGRESS event which should be handled by a separate handler with the same pattern(AchievementProgressHandler). In our case:
ACHIEVEMENT_PROGRESS_EVENT = {
type: ACHIEVEMENT_PROGRESS,
name: PROGRESS_INCREASE
payload: {
userID: 7,
achievementID: 10,
value: 10
}
}
Then, in AchievementProgressHandler we follow the same pattern:
AchievementProgressHandler: function(event) {
achievementCheckers = Map<achievementID, achievementChecker>;
/* update user.achievementProgress code */
switch(event.name): {
case 'PROGRESS_INCREASE':
achievementCheckers[event.payload.achievementID].execute(event.payload);
break;
case 'PROGRESS_RESET':
...
}
}
achievementCheckers contains a checker function for each specific achievement that decides if the achievement has reached its desired value(a progress of 100%) and should be awarded. This enables us to handle all kinds of complex cases. If you only track a single X out of Y scenario, you can share the function between all achievements.
The handler basically does this:
achievementChecker: function(payload) {
achievementAwardHandler;
achievement = db.getAchievement(payload.achievementID);
if (payload.value >= achievement.rewardValue) {
achievementAwardHandler.dispatch({
type: ACHIEVEMENT_AWARD,
name: ACHIEVEMENT_AWARD,
payload: {
userID: payload.userID,
achievementID: achievementID,
awardedAt: [current date]
}
});
/* Here you can clear the entry from user.achievementProgress as you no longer need it. You can also move this inside the achievementAwardHandler. */
}
}
We once again dispatch an event and use an event handler - achievementAwardHandler. You can skip the event creation step and award the achievement to the user directly but we keep it consistent with the whole history logging flow.
An added benefit here is that you can use the handler to defer the achievement awarding to a specific later time thus effectively batching awarding for multiple users, which serve a couple of purposes including performance enhancement.
Basically, this pseudo code handles the flow from [a user action] to [achievement rewarding] with all intermediate steps included. It's not set in stone, you can modify it as you like but all in all, it gives you a clean separation of concerns, cleaner entities, it's performant, let's you add complex checks and handlers which are easy to reason about while in the same time provide a great history log of the user overall progress.
Regarding the DB schema entities, I would suggest the following:
User: {
id: any;
trackingData: {},
achievementProgress: {} || [],
achievements: []
}
Where:
trackingData is an object that contains everything you're willing
to track about the user. The beauty is that properties here are
independent from achievement data. You can track whatever and eventually use it for achievement purposes.achievementProgress: a map of <key: achievementID, value: data> or
an array containing the current progress for each achievement.achievements: an array of awarded achievements.
and Achievement:
Achievement: {
id: any,
name: any,
rewardValue: any (or any other field/fields. You have complete freedom to introduce any kind of tracking with the approach above),
users?: [
{
userID: any,
awardedAt: date
}
]
}
users is a collection of users who have been rewarded the given achievement. This is optional and is here only if you have the use for it and query for this data frequently.
