I've decided to add some notification functionality to an existing app that
will be used across a small network, but I'm not sure how best to poll for
the event or to handle its acknowledgment.
For example, I have a "Completed" task that is saved to a tracking history
table. I know what employee I want to notify when that "Completed" entry
occurs, but how/when to "poll" the database for that entry? The easiest
method seems to be to make a check of the entries in the "Event" column at
the time the user logs on. It would be great to be able to make a check when
a job gets "completed" in the middle of the workday, but I can probably live
with the login "polling" (anyone have a better idea on the dynamic check?
Perhaps transaction sproc that fires a return value based on ...).
But what troubles me most is considering how to later indicate to a user
that the event has been retrieved, user notified, and acknowledged. This
*seems* to say to add another column to the tracking table to handle
acknowledgement of the notification, i.e., a bit "acknowledged" column used
in conjunction with the event column to stop the notification from firing.
I'm never keen to add another column to a table in an ad hoc fashion, but
this seems to be an exception.