TSQL Tuesday

T-SQL Tuesday 146: Where should business logic reside?

I am trying to get started on blogging again this year…T-SQL Tuesday came in handy with a great topic. This month’s host is Andy Yun(t |b). Andy’s challenge for us is to write about some notion ‘something you’ve learned, that subsequently changed your opinion/viewpoint/etc. on something. ‘

There are many things on which I’ve changed my opinion in my journey as a data professional. For this blog post, I picked a common one – where business logic should reside. During my years as a production DBA – there was (and probably is) a hard-held belief that databases are meant for CRUD operations only and business logic belongs in the application/middle tier. This belief has its place – DBAs don’t like debugging code that is application-specific, or be tasked with why data looks a certain way or what code caused it. Business logic can also be very complex and get deeply embedded in one place (the database). I have believed in this and fought for this at most places I’ve worked as a DBA.

Over time though, my opinion has changed. The fact that DBAs have to learn some degree of programming to survive today’s world has a lot to do with it. Also, I started to understand the nature of business logic better. I learned that a lot of things like foreign key constraints, not null constraints, triggers actually have to do with business logic and how the business defines its data. Databases usually outlive applications and less work is necessary when there is a move to a new application layer. It is much faster to make changes on the database side and roll it out compared to most application changes. It also offers single-point control of business logic, as opposed to it spread all over the place in applications.

And last but not least – learning business logic makes a person an important asset to the business. Most DBAs complain about being ‘just firefighters’…if we know business logic and have control over that, that makes us more than that – it makes us an important asset, in addition to helping us learn more programming as part of that process.

I do realize that there are cons to putting business logic in the database too. It can be very complex in some cases. The business may not have enough people to maintain it. It is a single point of control, therefore a single point of failure too if one looks at it that way. But now I believe there are more advantages to it than disadvantages. Thank you, Andy, for hosting.

2 thoughts on “T-SQL Tuesday 146: Where should business logic reside?

  1. I have frequently seen the decision made to keep all possible business logic out of the database on the assumption that the application will be the only access path to the data. However that assumption often proves false over time.

    A new version of the application gets built, and all that logic has to be re-coded (often without having been adequately documented the first time, making that re-coding a major project), or APIs get built to satisfy special requirements for integration (but the business logic is left out or only partially implemented), or operations or support people are given the ability to run scripts to implement “data fixes”, etc. If the business logic were in the database, there would be no circumvention of the business logic and no duplication of coding effort.

    Having some validation in the application to provide faster feedback to the user is sometimes helpful, but I think that in most cases, it should only be duplicating logic already in the database via data types, sizes, foreign keys, constraints, or even triggers (when written carefully and properly).

    Like

Leave a Reply to m60freeman Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.