MySQL’s Untapped Functionality

MySQL has long been the most popular open-source database solution for web designers. It is free, fast, stable, and widely supported. Until version 5.1 was released, MySQL's feature set was quite limited compared to the "big dogs" in the database industry, including Oracle, DB2, and MS SQL Server. Many of the new version 5+ features enable MySQL databases to operate more independently than before, because the database itself can process and validate data, rather than relying on outside software.

Here is the thing: many of these new features - including stored procedures, triggers, and advanced validation rules - are rarely implemented by web developers, even when seemingly appropriate. One explanation for this is that most web coders know just enough about database development to get the job done. They implement database (business) logic from within the web code merely because that is where they are most comfortable, not because it is good design practice.

But that's not really fair. There is sometimes good reason to keep even the core business logic in the web code. With web applications, a significant portion of the data handling is going to be implemented outside of the database whether you use stored procedures or not. Splitting up that logic between MySQL and web code adds complexity to the system architecture, and in practice makes it harder to debug and maintain. Plus, when you use Active Record libraries to handle database queries entirely in the web code, you can create web applications that can run on most major database platforms (MySQL, SQL Server, Oracle, DB2) with a "flip of the switch." Migrating internal database code like triggers and procedures from one database type to the next is less than straight-forward.

The right solution, like most things, hangs in the balance. Higher Media has experience building data systems that are meant to stand on their own, regardless of the software that interfaces with them. In those cases (or in cases where data processing performance is critical) it is a good idea to make use of stored procedures and triggers. We have also at times find it more appropriate to allow the model layer (link to MVC article) of the web application handle the logic. But no matter where the business logic is coded, it is a good idea to add data validation rules to the database table definitions as an extra layer of protection against erroneous data - something web developers don't do as much as they should.