Export to
Monday, October 6, 2014 at 11:36am and last updated
Tuesday, May 5, 2015 at 12:53pm.
PDX MUG: MySQL triggers
Access Notes
Doors to the lobby are locked at 5pm. Check with event organizer for access information.
Website
Description
Paul Irvine will be presenting:
Topic: using MySQL triggers to simplify application front ends, reporting queries, performance, batch processing, by denormalizing a data model in the triggers.
It sounds like something you might not want to do, most folks are taught normalization, never denormalization, but I’ve been doing this since the early 90’s on SQLServer, then on Oracle in the mid-90’s, and a few others, and of course MySQL since they introduced triggers. In truth, I learned about these techniques in the mid-80’s in some none-SQL proprietary relational products that had the features ‘built-in’.
A lot of people already use triggers for basic validation, and some people use them for more complex validation. A few even use them for some computed column value setting. That’s cool. The sorts of things I do are to replicate, propagate and maintain redundant copies of data, or to aggregate data. This usually makes user presentation many factors simpler for many application types, especially reporting, as it simplifies queries. And the cost to implement is marginal to none.
I’m a fan of using auto-increment keys in MySQL, because so many development frameworks like Rails, PHPRunner, Cake and others find it easy to use and address records in grids, and forms.
BASECOPYDOWN
In this denormalization, the child triggers retrieve a copy of the parent row’s relevant ‘natural’ key information.
Example : order detail rows retrieve the parent ‘order number’, the natural human order number, not the primary key auto increment. And that order number gets copied to a real column in the Order Line Item row. This means that reports, and user queries can very easily retrieve line items for orders using (human) order number now. And do real sorts. And the application code has zero work to do to gain this benefit. Three lines of trigger code do it all. And its guaranteed. SUMS
Very often, a parent row needs rapid access to the SUM of a column from its child rows. The simple way in a trigger would be to select(count()) on the child recs, and put the count in the parent row, but this has potential impact on the parent update. The cost of a query select against the child table. The way I do it is to put the code in the child triggers, to do an incremental update against the parent row. So as the child row is added, the new child value is added to the parent SUM column and updated. This gets more complicated on updates on the child, involving some difference computation and some stored procs to optimize the code, but the code is simple and templatized. And guarantees no select count() performance degradation, but gets the benefit of the running totals on the parent record for no more than one additional update.
And yes, I’ll be happy to share the template code for insert/update/delete trigger difference computation.
A similar process can be used to keep the count of child records in a parent table.
And of course these can cascade up and down more than one level.
Imagine a timesheet week or day entry that sums the values to the timesheet, that sums the values to the week aggregate record, or a group/project/division record of some sort for job costing… easy and trivial to do, but if that had to be done in application code, whether a middle tier or front end, it would get costly, and not guaranteed to be consistent depending on who updates what rows using what code.
The trigger guarantees robust integrity, minimal performance impact and maximum app benefit no matter whether its an app server updating, a front end updating, a mobile interface or an API.
Denormalizing is not only an efficient thing to do, it can save the front end developer some significant effort in data manipulation to assemble, select, filter and display aggregate, parent or child based information.