Business Logic versus Data Logic

One thing likely to trigger a spirited debate amongst software developers is how much logic should go into your database.

Many SQL-based databases — PostgreSQL and SQL Server have been my weapons of choice for the last 2 decades or more — have some element of inbuilt programming capability. In SQL Server, we can write triggers, functions and stored procedures in T-SQL (an extension of the SQL language providing control-of-flow capabilities) and in PostgreSQL we have similar capabilities in a range of languages including PL/pgSQL, PL/TCL. PL/Perl and PL/Python.

Using such capabilities, it is in theory possible to place all the logic of your web, desktop or mobile application in the database, with the application itself acting as a thin UI layer. Likewise, it’s possible to place all the logic in the application, and use the database as pure storage. Many systems are somewhere in the middle.

Often, this debate will be clouded by a developer’s capability. A developer who is unfamiliar with or doesn’t enjoy writing DB code will lean towards a thinner DB layer.

I’m approaching this subject as a software architect — someone who puts together plans for and oversees the approach to a software system — and basing my opinion on (amongst others) the assumption you have a ready supply of developers comfortable with the app and database environments of your choice. However, those assumptions don’t always hold true, and lack of available DB developers is an entirely valid consideration — you’ll get a worse outcome by forcing a developer to use an approach they’re uncomfortable with, even if it’s the “right” one.

The other thing to bear in mind is that “thinking in database” is a different skill to application development. I may make a further blog post on this in the future, but there’s a difference between database code that produces the right answer, and code which leverages the strengths and weaknesses of the database.

Motivation

Conventional wisdom (by which I mean, lots of people shout loudly) is that we shouldn’t put “business logic” in our database.

However, consider a typical business application — an order processing system for widgets. Initially, the company received mailed, faxed or telephoned orders which it recorded in a computer system; then a website was added. You’ve been asked to lead a full rewrite because the legacy technologies are now outdated, and to support ordering through a mobile app in the future. The CEO has heard lots about AI, and is also interested in people being able to order their widgets directly from Alexa or using a chatbot online.

There will be a lot of commonality between our 4 sales channels, but we might struggle to identify a common platform for all 4 user interfaces, which may well be developed by different teams or agencies. However, each will need to implement similar “rules” — such as crediting a customer payment against a corresponding order — which should be consistently applied across all platforms.

In their book Enterprise Integration Patterns, Gregor Hohpe and Bobby Woolf talk about 4 primary integration styles:

• File Transfer
• Shared Database
• Remote Procedure Invocation
• Messaging

These are primarily focussed around data, so a corresponding view of logic leads to the following integration styles:

• Duplicated logic — each application talks directly to the database (or files), utilising file transfer or shared database integration styles.
• Centralised logic — database — the database holds common logic across elements of the system, utilising the shared database integration style.
• Centralised logic — application — front end applications talk to a central back-end application, such as an API or transactional system, utilising RPI or messaging integration styles
• Centralised logic — common libraries — all applications use one or more common modules for back-end logic, utilising any of the above integration styles.
• Distributed logic — a number of front end applications talk to a number of back end applications responsible for different bits of back end, typically using messaging. For example, in an insurance scenario, separate back ends for underwriting and claims.

Each of these approaches has strengths and weaknesses, which I won’t explore in detail. However suffices to say that Centralised Logic in the Database provides a good trade-off between minimising duplication and not complicating the architecture with too many tiers of application and middleware.

Drawbacks of Centralising Logic in the Database

We’ve already covered one drawback — “thinking in database” is a distinct skill from “thinking in application”, and there’s no guarantee that the developer able to produce the stunning front end user interface you want for your online ordering is equally capable of (or enthusiastic about) writing T-SQL or PL/pgSQL. So, notwithstanding the push for devops or full stack, chances are if you’re talking about the database doing heavy lifting, you’re needing a team not an individual.

Another perceived drawback is testability — though not one I regard as insurmountable. A lot of work has been done on making application code testable at the UI and functional level, with techniques I am a keen proponent of such as test-driven development (TDD). Database logic is seen as inherently harder to test.

Rollout is another drawback many cite — when we change the database, that impacts all applications. This may, in many cases, be an advantage — such as updating the tax rate when sales tax rises — but can also be seen as a disadvantage if a logic change requires interface changes which may be a struggle to synchronise. Changes to our database can break all applications at once.

These, and other challenges, have led to a mantra — business logic should be in your application, not your database.

Business Logic versus Data Logic

Whether the mantra holds true depends on how you define business logic, in my opinion. I would separate the “rules describing how our business operates” into 2 layers — operational logic and data logic.

Operational Logic — this is the subset of business logic that governs how the organisation operates day to day. For example, in our widgets example, operational logic will dictate that widgets are packed into trays of 12 and cartons of 60. This logic will change responsively according to operational or tactical needs.

Data Logic — this is the subset of business logic that describes fundamental logic which will not frequently change. For example, the calculation of sales tax as a percentage of sales, the basics of double-entry bookkeeping matching a credit and debit, or the deduction of a cash withdrawal from a bank account balance.

Whilst I don’t believe changing the database has to be as slow or “risky” as some may say, it is inevitably slower and riskier than an application change. So therefore, we shouldn’t put business logic in the database lightly, and should be wary of putting logic into the database which changes frequently.

But, fundamental rules governing our data — which I’ve termed data logic — which is close to immutable, are different. We can, and I’d suggest should, put such logic in our database layer.

Logic in the Database Effectively

We shouldn’t ignore the drawbacks of putting logic in the database, but we can mitigate them. I can’t fully describe the approach we might take, without doubling or trebling the length of this blog post, but in summary we should consider:

• Including database specialists in our project;
• Version controlling our database;
• Test-driven database development;
• Coding database changes defensively;
• Continuous integration / continuous deployment of database changes;
• Test, staging and live environments;
• Automated quality gates for database changes.

Although these sound like obvious concepts for application development, I often see projects where some, most or even all of these disciplines are missing at the database level. In a future blog post, I’ll put these together into a workflow / tech stack.

Conclusion

Putting business logic into our database isn’t right for every project.

I typically won’t consider this approach when:

• I only envisage a single application or platform — the business logic can more straightforwardly be placed in the application or a platform dependency
• There are multiple data owners across the enterprise — this will generally indicate a distributed approach, with message passing between front-end and back-end (business logic) applications
• The team lacks database skills and it would be undesirable to bring specialist resource in — better to use an approach which more naturally “fits” with the team’s capabilities
• Business logic has dependencies on third party services — this will indicate a distributed approach or an API-based layer between data and applications
• The application should work with a range of database providers — procedural code is generally not portable between providers, so a SAAS product which supports SQL Server, PostgreSQL, DB/2 and Oracle will require maintenance of 4 separate implementations of logic, a daunting prospect. This will generally indicate an API layer.
• Scaling horizontally is a significant concern — an API layer or microservices architecture will be more straightforward to scale horizontally.

As with all tools, business-logic-in-the-database is not a silver bullet, but is an effective tool in the right use-case.