3

I'm creating an application in which people meet certain criteria to receive a payout at certain milestones. In order to earn these milestones, users will record activity in a log, and when they have recorded a certain number of activities, an administrator user is notified to confirm and authorize their payout.

Currently, we're planning to create a record associating a person to a milestone whenever someone saves the qualifying activity, and take it away on a change to that total.

I've got about 100 or so of these milestones. Their requirements are always X number of Y activity.

Since i have such a large number of milestones, what's the best way to approach checking each of these without a massive For loop or nest of 'if' statements?

7
  • I don't understand for what reason you want to delete the milestone-associating records. Why not leave the in the DB?
    – Doc Brown
    Mar 29, 2019 at 20:11
  • @DocBrown it is - as much as this makes me cringe - a requirement placed on me by the business.
    – Adam Wells
    Mar 29, 2019 at 21:52
  • 1
    Does not sound like a reason. Your "business" tells you requirements, and how you solve them technically is yours. If they do not want to "see" certain records in the application, then don't show those to them. I guess your business also wants the system to store the information if and when a certain payment for a milestone happened, which makes it a hard technical requirement not to delete person_milestone records (at least in my proposed solution). It would be helpful if you commented on that solution, if I guessed your data model right and if it could work for you.
    – Doc Brown
    Mar 30, 2019 at 5:53
  • 1
    when you say "take it away on a change to that total" do you mean that the total can decrease thus invalidating the milestone?
    – Ewan
    Mar 30, 2019 at 10:42
  • @Ewan This is correct. If a milestone requires ten instances of an activity, removing one of these instances elsewhere should disqualify you and remove the milestone.
    – Adam Wells
    Apr 1, 2019 at 14:38

2 Answers 2

3

You wrote

Currently, we're planning to create a record associating a person to a milestone whenever someone saves the qualifying activity

which means, when you are going to create that record, you have the particular person ID and the particular activity ID at hand.

Since i have such a large number of milestones, what's the best way to approach checking each of these without a massive For loop or nest of 'if' statements?

I assume you have the following tables in your model:

  • person (with a person ID and descriptive attributes)

  • activity (with an ID and descriptive attributes)

  • person_activity (link table holding a person ID, activity ID and a counter)

  • milestone (each one with an activity ID and a number describing the number of activities required to reach this particular milestone; the combination of both should be unique; also a milestone ID as primary key, and maybe the payout amount)

  • person_milestone (the link table you want to populate with new records, each one holding a person ID, a milestone ID and a payout state/payout date field)

If the counter in person_activity is always updated one-by-one, things are simple: whenever that counter is touched, start by querying for the milestones which may be triggered by the given activity ID and match the activity counter. If that query returns a milestone, create the related person_milestone record (with an initial payout state like not paid).

That requires no for loop and no nested ifs, it is a very simple SQL query.

Afterwards, there can be a process to query all person_milestone records where the payout has not happened so far and inform the administrator about it. When the payout is done, the particular person_milestone record gets updated (payout state = 'paid', maybe with recording the payout date). There is no need for ever deleting those records again.

0

Currently, we're planning to create a record associating a person to a milestone whenever someone saves the qualifying activity, and take it away on a change to that total.

This is as bad an idea as saving someones age in your database.

Here's why: If you create a database that has an age field then every day you have to do a query to find out who just had a birthday and update their age field. Instead just store their birth date and calculate their age anytime someone asks.

So what if rather than creating a soon-to-be-stale record that associates a person to a milestone or creating a massive for loop what if we could just design a simple milestone report?

List<Activity> activities = new ArrayList<>();

Milestone octo = new Milestone(80, 90, "octogenarian.gif", payout, output);
activities.add( new Activity(octo, "Age", calcAge("3/29/1935") ) );    

Milestone nice = new Milestone(10, 25, "nice_answer.gif", payout, output);
activities.add( new Activity(nice, "Votes", 5) );

User user = new User(activities);
user.displayBadges();

This is flexible enough to let you add new milestones and new activities. It wouldn't be much trouble to change it so multiple milestones can be checked against a single activity.

Done this way the DB doesn't need to understand milestone business logic at all. It can stick to recording data.

4
  • Can you clarify what the constructor is representing? I think i know, but I can't quite put my finger on what the first two arguments correspond to. Its the range for the number of activities, right? Additionally, how would this solution store the payout date? This is really slick, just something's not clicking for me.
    – Adam Wells
    Mar 29, 2019 at 19:48
  • Yes that's the qualifying range. Give me the requirements for a "payout date" and I'll show you how to add it. Right now I'm unsure what you want. Mar 29, 2019 at 19:52
  • Oh, duh. That -would- have made sense to put that... y'know. in the question. The payout date is just an extra field that tracks the date the user was paid for the milestone in question.
    – Adam Wells
    Mar 29, 2019 at 19:59
  • Note edit. Understand that this is a report. It doesn't store the payout date. What it can do is construct a payout object that will display 3 states: nothing, payment pending, and paid on such and such a date. Show that to the user or the admin who will confirm and authorize the payout. Update the DB when authorization happens. Mar 29, 2019 at 20:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.