Using update and insert triggers to provide a coun

Question

I have a table 'user_plays_track' that keeps track of how many times a user has 'played' a track.

I use the following query to either insert a new track a user has played, or update the number of times an existing track has been played:

INSERT INTO user_plays_track
(user_id, track_id) VALUES (x,y) 
ON duplicate key UPDATE play_count = play_count+1

Here is the structure of my table:

user_id  track_id  play_count
1        5         2
4        2         1
3        5         7

From this information, I can infer things such as the total number of times a track has been played, or the total number of plays an artist has had, by finding the sum of all the track counts.

With a thousand or so records, this would soon become messy and the semantics unclear. What I wish to do, is use triggers to produce what could be described as cache.

For example, when a record is updated or inserted into 'user_plays_track', the 'tracks' table will increment its play_count column, indicating the total number of plays from all users for that track.

track_id  artist_id   track_name  play_count
2         1           Hey         1
5         1           Test        9

Furthering this, another trigger should be applied, to infer new knowledge such as the total number of artist plays. This would again be triggered when a new track is added, it will find the artist_id the track belongs to and update the 'artist' table accordingly.

artist_id  artist_name play_count
1          Bob         10

How would I go about implementing the relevant triggers, to provide a incrementing totals when a user 'plays' a track?

Answer 1

The more you want to calculate at query time, the more you want views, calculated columns and stored or user routines. The more you want to calculate at normalized base update time, the more you want cascades and triggers. The more you want to calculate at some other (scheduled or ad hoc) time, the more you use snapshots aka materialized views and updated denormalized bases. You can combine these. Any time the database is accessed it can be enabled by and restricted by stored routines or other api.

Until you can show that they are in adequate, views and calculated columns are the simplest.

The whole idea of a DBMS is to store a representation of your application state as the database (which normalization reduces the redundancy of) and then you query and let the DBMS implement and optimize calculation of the answer. You haven't presented a reason for not doing that in the most straightforward way possible.