Creating this results database was at first a personal project for me during the 2006 Federal Election, and then I decided to publish it on the web for my friends to use (and for me to learn PHP-MySQL-Ajax-CSS with).
About the Data
Note that the data originates from Elections Canada & Statistics Canada, but has been “normalized” — cross-referenced in other words. This means that you can work backwards from a candidate’s name to see the many different elections, ridings, even parties s/he has been associated with. Or see how a party has performed over time. Or examine the changing voting patterns of a certain region.
This cross-referencing of candidates did entail a lot of manual work which can always introduce errors (duplicate entries not consolidated, or erroneously consolidated), for which I am solely responsible. I also added the candidates’ genders whenever it was obvious, or if not obvious to me, whenever I could confirm it by searching the Internet.
The Transposition information I also manually entered from a printed Elections Canada document.
People who know more about regional trends in Quebec and New Brunswick (or anywhere else for that matter) should feel free to point out riding groupings that make no sense, or suggest better names for any existing groups (surely there’s a better name than “GVRD Fraser North East“, for example).
I appreciate being gently corrected on any errors, and will fix them as quickly as possible. Please send any comments, gripes, suggestions or kudos to me via the Contact page.
As the data itself is already in the public domain, I ask only that you credit the Pundits’ Guide for any additional analysis, calculations, queries or metrics offered here.
About the Database
I maintain a relational version of the database in MS Access, and had been periodically updating a set of de-normalized views stored as MySQL tables on the server of my web-host (I’m hosted by Sibername, an Ottawa-based Internet Service provider). The numerous updates to the 40th General Election nomination data forced my hand on that strategy, however, and so I bit the bullet and normalized the MySQL 5.0.x database schema, teaching myself the MySQL syntax for writing views in the process. A further upgrade from 5.0.29 to 5.0.49 introduced some collation order errors, which have since been rectified.
I have to say though that it’s been disappointing to realize that MySQL views are not truly “compiled” in the sense that they are unable to take full if any advantage of the underlying table indexes (especially when two or more views are joined together), and they thus create more problems than they solve. Until this shortcoming can be addressed, MySQL will just not be the database engine of choice for any serious database developer.
The web interface was originally coded in VBScript/ASP and hosted on my home computer, but has now been migrated to PHP 5 and completely re-organized.
The website was recently migrated for me, from the Blogger platform plus hand-coded pages into a WordPress version, by Patrick Denny of Atomic Noggin Enterprises. (His website says he builds “funky sites”, and now he’s building a “funke” one as well.)
About the Author
Alice Funke graduated from Carleton University in Political Science, and is a business intelligence specialist for the federal government in Ottawa.
During the 2008 election, she took a leave of absence and worked as a computer-assisted reporting consultant for the Parliament Hill bureau of the Canwest News Service.
She is also the programmer for the PACS Canada Media & Legislator databases.