1

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?

3

I would go with the view for a few reasons:

  • It is obvious how the sum is generated with a view and it would always be correct.
  • You won't be able to fully trust the data of a updated column. ie life happens. (The importance of this depends on the consequences of bad data.)
  • This is essentially a premature optimization in a relatively lightly used system.

I don't think there is a good reason given the circumstances you described to go with the column/trigger/stored proc at this time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.