[smilliken]

> DON'T HANDLE THIS IN YOUR DATABASE LAYER.

I implore you to reconsider. I used to hold this opinion very strongly, have since changed my mind, and began to question why I ever held it in the first place. Now I realize it was rather simple: the first few teams I worked on had a database administrator that wasn't one of the engineers. We would avoid doing anything in the database because the iteration cycle would take months, involve many emails, and we'd end up with something different than what we wanted. In other words, it was a social problem, not a technical one.

If you empower engineers to change the database, these problems go away. At MixRank, we deploy a handful of schema migrations every day— they're a normal part of our development flow. In particular, we embrace constraints (who doesn't like avoiding invalid data?), and triggers extensively. Once we started using triggers, we noticed all sorts of awkward and inefficient patterns in our code that were contortions to avoid adding a single function into the database.

[kbenson]

I'm currently really feeling the limitations of MySQL's auto-increment and of InnoDB's inability to deal with constraints that include multiple tables for a complex data source I am trying to efficiently represent in a local schema. Having a trigger which calls a function seems so much more powerful (if admittedly more boilerplate for the simple case).

I've always shied away from UDF, but that's more from not wanting another language to deal with and since there's fairly poor support for them in MySQL (which I'm stuck with). For example, the default mysqldump options for all databases don't include UDF unless you specifically request them with--routines. :/