How to optimize constant querying for a sum of many rows? I'm making an app that is supposed to view election results (yep, homework assignment). For each of the 24681 wards and each of the 12 candidates the number of votes the said candidate has received in the said ward is to be stored.

The most common operation, of course, would be viewing voting results aggregated over all election wards. Also there must be separate tables for each province, electoral district and municipality. Viewing aggregate voting results for each of these areas must also be possible.

Aggregating election results over the whole country takes up time measured in seconds. I consider this inappropriate even despite it's only a homework assignment. If this app was to be exposed to general public it would be unusable. I think it is broken to require aggregating results over 24681 wards each time a user loads the main page!

One way to counter that would be to store aggregated results for each territory (ward, municipality, province, district, whole country) in the database. This, however, would break normal forms pretty badly and I'm not sure if that's acceptable.

I'm thinking about database views. I'm imagining that the database would cache the results of views just as if I would break normal forms by adding relevant tables and columns myself and automatically recreate this cache for each larger area each time election results for the smaller area are modified (yes, editing the number of votes for each candidate in each ward is required). Can I make views work that way?

How else can I combat this issue?
The concept you want is called a "materialized view". Normally, a view is fundamentally just a query prepared in advance, that you can reference like a table for convenience. A materialized view is at least partially stored in the database.

In SQL Server, this is done by creating an indexed view. This acts as an index tied to the table(s) in the view, and is updated when the underlying rows are updated, just like any other index. Note that the SQL Server Developer's Edition is free, and has very few limits on it.

Near as I can tell, neither MYSQL nor PostGreSQL provide this feature in this form.

With such a materialized view, you still have a potential for a performance issue. Since the index is updated every time the underlying rows are updated, if you're trying to tally up votes in real time, you may find that the view is constantly being modified. Between frequent SELECTs against the view, and constant INSERTs and/or UPDATES on the underlying tables, you may face blocking issues, a major problem if it prevents you from showing results, and an even worse problem if it prevents votes from being entered in the first place!

You may want to think about another option. Consider if you need a real-time tally, or if one that's been updated recently (say, within the last 5 minutes) would be sufficient. From what I've seen in election results in the US, that's the path they seem to take.