A room with a view
Whilst not being one to make gross generalisations (heh!) I like to think there are two schools thoughts on databases – those that use “extended” features such as triggers, views and temporary tables, and those that don’t. I, for one fall firmly in the latter – usually.
However over the past few days a major project that I’ve been working on brought forth a requirement for some hardcore reporting. Due to the database structure that was required (there was a lot of dynamic fields and association tables) doing it in ActiveRecord was near impossible – in fact, doing it in native SQL was equally painful.
Quite jokingly, MySQL views were suggested, but then in a cold flash back to my days working in Government environments, reminded me that DBAs used views for this stuff all the time, so time to investigate.
The idea is pretty simple: a view is a virtual table that is based on a SQL query that you can run queries against, which means you can easily flatten associated tables and turn complex search queries into simple ones. Example…
Say you have a structure that looks something like this:
(Excuse the diagram – My windows laptop is in the other room and the graphics editors in Linux are balls)
So as you could imagine, you may have companies that have many projects which in turn have many shifts. Who would you calculate all the shifts from a particular company? You would probably end up with something like this:
SELECT shifts.id, shifts.start, shifts.end FROM shifts INNER JOIN projects ON shifts.project_id = projects.id INNER JOIN companies ON projects.company_id = company.id WHERE company_id = 4
Which, whilst fairly simple, is a pain to write – and it can only get worse if the data model becomes more complex. This is where views make life so much easier. By creating a view using a simple query:
CREATE OR REPLACE VIEW shift_reports AS SELECT company.id AS company_id, company.name AS company_name, project.id AS project_id, projects.name AS project_name, shifts.id AS shift_id, shifts.start AS shift_start, shifts.end AS shift_end FROM shifts INNER JOIN projects ON shifts.project_id = projects.id INNER JOIN companies ON projects.company_id = company.id</p>
we now have a virtual table called shift_reports with columns: company_id, company_name, project_id, project_name, shift_id, shift_start and shift_end ehic you can query just like any other table. (I am aware that the query is much longer than the one we are trying to replace, but you only do it once per database, and the example is contrived – humour me). An example query would be: SELECT * FROM shifts_reports WHERE company_id = 4 – much cleaner! Where this becomes even cleaner is if you are trying to link this up to a search form – everything matches up with a much bigger bow (especially if you are using a framework like Rails).
Whilst on the topic of frameworks (like Rails) – because it is exposed as a regular table, you can point ActiveRecord at it – just create a corresponding model and find until your heart is content, just don’t try to modify the records, as it will fail miserably (views are read-only).
So next time a client asks you to create an impossible report, your cold sweat may be slightly less shiver-inducing…