So it seems a bit strange to say this, but most developers I know hate the idea of Views, Stored Procs and Custom Functions built into the database system. The simple reason for this is:
Application and Business Logic, belongs in the application.
Although I completely agree with this, my time spent recently with a highly skilled DBA has taught me that sometimes, you want to help shape what the developers see from the database. There’s a number of reasons that this works, firstly, you can support cross database queries in a highly controlled and secured manner - bonus, and secondly, you can apply additional logic or functionality that assists in the developers being able to quickly and easily map information in a complex way.
As an example, we control ‘active’ and ‘inactive’ database rows (yes, each row on every table) with a begin and end number. This may seem like a huge waste of time, but we keep meticulous records - so instead of deleting or updating, we create new rows with new numbers and close off the old ones.
This solves two problems, firstly, we don’t have to keep a ‘log’ of changes, as we can plainly see them with very simple SQL queries and secondly we have a complete history of records for the business boffins to be able to roll into nice reports that show the progressive nature of ‘change’ in our data.
Thus, we archive old data fairly regularly and we do this by looking at these numbers to ensure that we clear off anything we haven’t used in a while.
Okay, straight forward, but what does this have to do with views, stored procs and custom functions? Well, it’s simple, recently I have started writing views, in place of tables, that provide the records in an easy to use way, that automatically check for active records and inactive records, so that the developer doesn’t have to do this each time.
This doesn’t limit the developer from accessing the tables directly, but it does give them a great, quick and simple way to do something rather mundane, that we do constantly.
So in summary (brief and fairly non-descript), don’t discount this particular group of functionality from within mysql just because it belongs in the application .
Enjoy!