I've got a couple of tables to track volunteer workers at our non-profit.
One stores Volunteers and tracks information about a volunteer such as the person's name, a contact number, primary location and such.
Another table stores Tasks and tracks specific work done by the volunteers. It references the Volunteers table by ID to allow me to, say, pull back all of the tasks performed by Pete.
Tasks have number of hours so we can track how much work was done on tasks at a certain location, how many total hours were volunteered at that location, etc.
I'd like to keep track of the cumulative amount of volunteer time for each volunteer and this where a design decision needs to be made.
I could add a column to the Volunteers table called VolunteerHours and then add triggers to the Tasks table to call a stored procedure to recompute the volunteer hours when rows are added/updated/deleted on the Tasks table.
Alternatively, I've considered creating a view that would be used to retrieve the totals on demand.
create view VolunteerSummary as select v.VolunteerID, SUM(TaskVolunteerMinutes) as VolunteerTotal from Volunteer v join Tasks t on t.VolunteerID = v.VolunteerID group by v.VolunteerID go select * from VolunteerSummary
The view approach is attractive to me because I'm not storing a total column on the table, rather computing the total when I need it.
Assumptions I have made about these tables:
- Fairly small (< 1000) number of volunteers
- Most volunteers volunteer less than 12 times a calendar year (once a month)
Based on my assumptions and description, is there a good reason to go with the column/trigger/stored procedure route over the view?