Multiple clients can modify the DB and all need to be notified if a DB change occurs. Where I work, this is implemented with application threads that poll the DB every second or so. This doesn't strike me as a good way to do it, so I was wondering how do people usually handle this?
Polling is the most common solution; the other is to have your middle-tier handle the notifications to other services when updates are sent through them. If you have a bunch of clients sharing a database and you can't change them all then for SQL Server you could probably do this with triggers that input messages to the Service Broker and configure other listener actions to notify your clients. I wouldn't start down that road unless you were really confident you need to do that, polling is likely to be better for most projects. You have to consider how this state gets reflected in your clients; most web and client/server technologies are more or less a request/response model so even updating a middle-tier would not matter until your client polled that middle-tier for status.
Databases are representers of facts. They are not message queues. If you want to know what the current state of your interests, you should consult a database. If you need to know when the state of your interests have changed, you should instead subscribe to a messaging system which the parties that make changes publish to.
At our organization, we use RabbitMQ, an AMQP broker. Others exist, such as ActiveMQ (for amq) and ejabberd (for xmpp).
Using such a solution obviously requires that changes are fed through the message queue in addition to being persisted to the database. A reasonable implementation might look like this:
You will be very happy with the results of integrating a proper messaging queue into your ecosystem. It makes adding and removing functionality, and adapting new functionality much easier. For example, if the transaction to update the database is slow, but reliable, another actor could instead subscribe to the