I need a way to store the count of related rows on a separate table for the related fields, but I would like the information to be up to date and not use too much processing power.
The idea is this
Table Album: id: 1 name: "Some Name" artist: "Some Artist" tracks: 15 Table Tracks: name: "Some Track" album_id: 1
Note: This is both fictitious data and schema
So now what I would need is for the tracks column to be updated automatically depending how many tracks there are with the same id as the album, I'm going to add the data with an import script so I know I can run a stored procedure at the end of it if necessary to count all the tracks and update the table.
My biggest problem however is that if a track is deleted or added outside of the script the actual count will be off, I was thinking of adding a trigger but that would mean that it runs every time a query is ran, and that would just make it very intensive (I'm guessing).
The main reason I want to have the count on the other table is because there's gonna be a lot of queries ran that will have to compare say how many tracks each album has, and only select albums that have over X amount of tracks.
Now I'm open to using a query that will do the count at the time of the query execution but I don't know if that will have too big of an impact on the processing power, each "Album" might have hundreds of tracks and there will be hundreds of albums, and it will exponentially get bigger over time, so I don't know if that will become an issue.