How to update multiple information at different locations(table) on change of a record?

Question

This is not SO Meta question. I am using SO only as example.

In StackoverFlow each answer, each comment, each question, each vote has a effect which produces a badge at some point of time. I mean after every action a list of queries are tested.

E.g. If Mr.A up votes Mr.B Answer. So we have to check is this Mr.B's answer upvoted 100 times so give the Mr.B a badge , Has Mr.A upvoted 100th time so give him a badge.

It means I have to run at least 100 queries/IfElse for each action.

Now my real life example is I have an application where I receive online data from an attendance machine. When a user shows his card to machine. I receive this and store it as a record. Now based on this record I have multiple calculations.i.e Is he late. Is he late for continues 3 days. Is he in a right shift(Day shift/Night Shift). Is today holiday. Is this a overtime. Is he early.......etc.,etc.,etc.

What is the best strategy for this kind of requirements.

Update: Can SO team guide us on this?

Answer 1

Couldn't you just use "flags" (other tables, other columns, whatever) to indicate when those special cases occur? That way you would only have to do one lookup (per special case) than a ton of lookups and/or joins. You could record the changes (third day late, etc.) on insert.

Answer 2

You use queues and workflows. This way you decouple the moment of the update from the actual notifications, allowing the system to scale. Tighly coupled, trigger based or similar solutions cannot scale, as each update has to wait for all the interested parties to react to the notification. The design of processing engine using workflows allows to easily add steps and notifications consumers by changing the data, w/o changing the schema.

For instance see how MSDN uses queues to handle similar problems with MSDN content: Building the MSDN Aggregation System.

Answer 3

Also, what to check depends on a threshold.

e.g. Is a person absent from last 3 days? That check is required only when the person is absent for 2 days.

I mean - you need not check everything, everytime.
Also, how much of info needs to be updated immediately? SO doesn't update things real time.

Answer 4

May be you must use two databases with online replication between them - one for getting realtime data and nothing else, in second you may use hard calculations (for example calculate all latings every 10 minutes or by requsts). Locate this databases on different servers.