Reporting….can be a pain. Business requirements for reports often collide with domain models, and leave developers annoyed. Why do you need the first 10 users of every Wednesday, unless their first name is Steve, for the past 6 months, excluding October? MySQL temporary tables can help.
Doggie Nail Clippers, LLC
Reach back to that great sci-fi imagination of yours, and imagine you are developing a doggie nail tracking application, which tracks information about all your doggie clients. Based on the ridiculous needs of the application, here is your DB schema.
CREATE TABLE `dogs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `legs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`dog_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `nails` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`leg_id` int(10) unsigned NOT NULL,
`last_clipped` datetime NOT NULL,
PRIMARY KEY (`id`)
)
Don’t worry about how this data as used, as I just made it up and really have no idea. (All you experts are probably saying, why aren’t there any foreign keys. Well, I am using MySQL 6.0 beta and ran into a few issues, and gave up)
Your product team comes to you one day, and says they really need to send a newsletter every week, to try and boost the amount of doggies you see daily. So they ask for a report of dogs. Here are their requirements:
Dogs with nails clipped over 3 months ago
Dogs with less than 3 legs, because they need clipping more often
Dogs with more than 5 nails, because that’s just weird
Dogs in a list of names, because certain names correlate highly to long nails
You have two options, fight the requirements, or do the thing. If your product team is good, no matter how ridiculous the requirements are, there is probably a good reason.
As the developer, you are now tasked with getting this report, which is due last week. It might be possible to create one amazing, beautiful, unmaintainable query to accomplish this. But why strain for hours, creating a query that is probably screwed up in some weird edge case because 4 subqueries, with having statements are just too much to handle. Instead, you should try a temporary table.
create temporary table dog_report
select d.id, d.name
from dogs d
inner join legs l on l.dog_id=d.id
inner join nails n on n.leg_id=l.id
group by d.id
having MIN(n.last_clipped) < DATE_SUB(NOW(), INTERVAL 1 MONTH);
insert into dog_report
select distinct d.id, d.name
from dogs d
inner join legs l on l.dog_id=d.id
inner join nails n on n.leg_id=l.id
group by l.id
having count(n.id) > 4;
insert into dog_report
select distinct d.id, d.name
from dogs d
inner join legs l on l.dog_id=d.id
group by d.id
having count(l.id) < 4;
insert into dog_report
select d.id, d.name
from dogs d
where d.name in ('clippers', 'likes some nail clipping');
select DISTINCT * from dog_report;
Bam. Multiple queries, with all the work handed off to MySQL instead of your code. This allows MySQL to do what it is good at, data, and you to do what you’re good at, code. And if I was a sweet developer, I might even make some report builder system with each requirement coded as a completely pluggable constraint.
At this point, you might want to know a little more about temporary tables. They are just like regular tables, except they exist only for the current session, and are dropped when the session ends. If you are interested in performance, or detailed information, I can’t really help. But, MySQL probably has all that and they are a lot better at explaining it than I am.
The power of temporary tables, in my opinion, is they allow you to leave you domain/relational model intact, while still being able to accomplish tasks that don’t care about that model. So, you the developer can stand in awe of your model, while the product team can stand in awe of the number of doggies coming in to get their nails clipped.