Table Design for user-specific and user-agnostic criteria

database-designforeign key

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?

Best Answer

Track your achievements in parts. Right now you have some indicative (i.e. descriptive) information and one "business rule" column (Metric).

You actually need more than one business rule column. Depending on what you foresee being your needs and how much work you want to put into now, this might be either one or two more business rule columns, or possibly even an extra table or two.

In addition to "how many are needed?" (i.e. Metric) you also want a column that describes how many of what? You have two descriptive columns for this now, but these are human readable. You also want a machine readable column. In terms of nomenclature, I would actually use Metric for the what, and something else, say Scalar for the how many.

With a Metric column and a Scalar column you can then write program logic that knows how to measure each type of achievement. This way you don't need custom logic for different levels.

You could take this a step further and normalize this somewhat so that the descriptive information is part of a separate table. This would allow you to have groups of achievements that share certain types of information and logic. This distinction would help you with handling user-specific and user generic logic in a nice, managed, organized way.

Related Question