There is 'team_sector' table with following fields: Id, team_id, sect_id, size, level
It contains few records for each 'team' entity (referenced with 'team_id' field). Each record represent sector of team's stadium (totally 8 sectors).
Now it is necessary to implement few searches:
by overall stadium size (SUM(size));
the best quality (SUM(level)/COUNT(*)).
I could create query something like this:
SELECT TS.team_id, SUM(TS.size) as OverallSize, SUM(TS.Level)/COUNT(TS.Id) AS QualityLevel
FROM team_sector
GROUP BY team_id
ORDER BY OverallSize DESC / ORDER BY QualityLevel DESC
But my concern here is that calculation for each team will be done each time on query performed. It is not too big overhead (at least now), but I would like to avoid performance issues later.
I see 2 options here.
The 1st one is to create 2 additional fields in 'team' table (for example) and store there OverallSize and QualityLevel fields. If information if 'sector' table is changed - update those table too (probably would be good to do that with triggers, as sector table doesn't change too often).
The 2nd option is to create a view that will provide required data.
The 2nd option seems much easier for me, but I don't have a lot of experience/knowledge of work with views.
Q1: What is the best option from your perspective here and why? Probably you could suggest other options?
Q2: Can I create view in such way that it will do calculations rarely (at least once per day)? If yes - how?
Q3: Is it reasonable to use triggers for such purpose (1st option).
P.S. MySql 5.1 is used, overall number of teams is around 1-2 thousand, overall number of records in sector table - overall 6-8 thousand. I understand, those numbers are pretty small, but I would like to implement the best practice here.
