Strategy to make sure relevant cache records are expired on data update

Question

In our database, we have around 150 different tables. However, there are a handful of them that are key, as in they get referenced pretty much everywhere. We also have quite a few reports that we pull across a lot of these tables and for a few of the items, we perform some calculations. We thought it best to just create a couple of simple flat tables in the database for caching purposes.

At first, it was fine, but now it's kind of getting out of hand when it comes to expiring the caches. For example, let's say we have a Vehicle table and we might have a report which fetches VehicleCrashSummary from 10 different related tables for a vehicle and includes some calculations. So we created a cache table for that and we expire the records in VehicleCrashSummary for a particular Vehicle Id when Vehicle is updated via VehicleController. We also need to do the same when a record in CrashRecord table is updated for the same Vehicle id in CrashRecordController.

We had initially done this for Vehicle across many controllers, and now we are working on another summary which is not focused on Vehicle, but on VehicleOwner. As you can imagine, this will now not only need to track the tables related to the owner, but also a few of them related to the Vehicles related to them. We are basically looking at a cascading situation and it will be very difficult to keep track of which records to expire when a table is updated.

What I am asking is: is there a better way of doing this?

Answer 1

If I understand your question correctly, you seem to commuincate the change to a record via a direct notification to all intersted parties, as in:

updateVehicle(Vehicle v) {
    vehicleRepository.update(v);
    reportControllerA.vehicleUpdated(v);
    reportControllerB.vehicleUpdated(v);
    ...
}

Which basically leads to a change in the controller, whenever a new report controller is added.

In this case, using a messaging service and moving the responsibility to the report controllers is an option you might look into. Basically, broadcast a message from the controller:

updateVehicle(Vehicle v) {
    vehicleRepository.update(v);
    messageService.broadcastVehicleUpdated(v);
}

whereas the report controllers subscribe to this topic and take all necessary actions.