There are several organizations who practice a peculiar activity. Those businesses develop web apps where all the business logic is stored in database procedures. This practice, while fully operational in theory tends to be of negotiable value in practice. Here’s an example: HTML that is used for view as controllers thus client requests are diverted to appropriate databases. This choice may seem quite acceptable when your business logic significantly relies on databases. In theory.
Pros and cons
Now that we have touched theoretical aspects of storing business logic in databases we must discuss potential pitfalls and possible benefits. Let’s begin with the good part. Pros:
- Business logic is comfortably stored in one place.
- Stored procedures may be of worth and value to multiple apps.
- Also apps should be fatter as numerous SQL queries are being done throughout just one round over the database.
- There will be lots of load on the database. Load that isn’t even a required necessity and may be otherwise avoided.
- All involved developers will have to be fluent with the SQL dialect that’s used in your particular case.
- SQL code lacks expression and flexibility thus making it more difficult to develop functionality that covers higher level concepts which have no direct relation to data.
The real deal
Well, I could even say advantages overweight possible flaws and the process does indeed seem like a valuable addition into your web app development processes. However we have covered how things work in theory. What’ll we have when stuff gets real?
- The largest catch are developers themselves as few can really create appropriate, consistent and properly-stored procedure interfaces. Especially with respect to the fact such an interface has to flawlessly work across several apps. Things escalate from bad to worse when it comes to documentation as there are simply too many stored procedures.
- SQL is a programming language with a huge emphasis on the programming part with lot’s missed out from lingual capabilities. Thus requires tons of effort to run, maintain and fully understand the code. And development of valuable functionality takes much longer that the same task would take with implementation of other languages.
- Bottlenecks are any database server’s main escort. Adding more load can turn out as a disaster. Proper balance is hard to achieve and even harder to maintain.
- What if a variety of SQL servers or even one different server is required by the customer? Well, the process will be forced to begging from scratch and the same will happen over and over again for any new SQL server because you are too addicted to your primary database.
- Same may happen if Microsoft will cut down several functions you are actively using in all stored procedures.
- More synchronization challenges, tools that are complex as hell, source control is an actual headache and much more.
There truly are several particular cases where stored procedures are a reasonable option. If there are such activities involved as generation of reports that require enormous additional processing investments from several large tables than it’s ok as you probably don’t want your app to perform hundreds SQL requests, right? Business logic may be stored in database procedures, yet these cases are rare. Otherwise, stick to your usual plan.