I want to design a table that is general enough to accommodate an increasing number of achievements as I come up with new accomplishments to reward. Initially, I thought to design the table as follows
- Name (of Milestone)
- Description (of Milestone)
- Metric
So for the last milestone listed above, I might say
- Name: 1k All-Star
- Description: Completed a total of 1000 tasks
- Metric: 1000
The code would check the number of completed tasks against the metric to determine whether to award the milestone. But there are two types of milestone examples I listed. One in which the metric is universal for all users, and one in which the metric is subjective and depends on the user's previous actions.
Would I want to define universal metrics on one table and make a separate milestone table for the user-specific achievements? I imagine there that I might create a secondary table of these user-specific metrics so as not to repeat fields:
Subjective Milestone Table
- Name
- Description
- Metric - one-to-many connection to Subjective Metric Table
Subjective Metric Table
- Name
- User
- Metric
So as an example
- Name: Weekend Warrior
- Description: Exceeded the record for most number of tasks completed on a Sunday
Metric: 5
Name: Weekend Warrior
- User: Samson
- Metric: 5
Or am I going about this all wrong?