Business logic: Database vs code [duplicate]

Question

I'm a student of systems engineering, and all my teachers and friends (that actually work in the area) say that it is better to have as much logic as possible implemented in the database (queries, views, triggers, T-SQL, etc.). I think that it's better to have it in the code.

Their reasons are:

• If they need to change the language, almost all logic will be in the database; therefore the time of implementation will be minimal.

• Changes in the language are more common than in the database.

My reasons are:

• It is obvious (in the current environment of my country at least) that they do not change the language of the projects that "easily." (I've seen programs that are still in FoxPro, because if it works, there is no need to change it).

• Programming languages are about functionality, while databases are about data. You can have programming functionality in databases, but I think that it should be limited to the components that affect data.

• It is easier to implement new requirements (for example: If the customer wants an API).

• Normally when they use logic in the database, the rest of the logic that is implemented in the code is more spaghetti-like (random functions, for example).

• Generally, it is more usual to have more programmers than database administrators (DBAs).

  Which implementation is the best?

Answer 1

See How much business logic should the database implement? for previous discussion.

In general, everyone wants things done in the layer they control. Because then they control it.

Every database vendor wants people to put as much logic into the database as possible. Because that locks you into the database. The reasoning is that if multiple applications use the same database, they will reuse code.

However programmers emphatically disagree. Databases offer poor programming options. Deploying code to databases is hard. Databases lack basic tools for revision control, interactive editing, deployment and unit testing. Stored procedures tend to involve horrible to debug action at a distance. It has become less common to have multiple applications hit the same database. And if you ever have to make something scale, the one bottleneck that is hardest to fix is your database.

My bias is clear. I'm a programmer.

But I've been programming for close to 20 years, mostly as a back end programmer who is responsible for data. I've seen the argument many times for moving logic into the database. I've seen systems that did it, and systems which avoided it. I've had to migrate databases, migrate code bases, etc, etc, etc.

The worst messes have always been when business logic was in the database. They were always the hardest ones to fix. And I can say that while I've many times encountered the claim that "we moved logic into the database for performance", performance is almost always better with a clean normalized data model, good indexes, a caching layer in front of the database, and sane algorithms implemented in a modern programming language.

Answer 2

I am very firmly of the view that when ever possible, business logic should be kept in the software layer and not the database layer. Note, that when ever possible falls far short of always.

There are strong arguments for both ways, and as always use engineering good judgement to decide for each project how much weight should be applied to each point before deciding which is the more appropriate choice.

(as other people make sugestions in the comments, they can be added to the list)

Arguments for the database handling business logic:

• Business logic needs data to operate on. Getting the logic processing as close to the data gives better performance
• One place to apply updates

Arguments for the software layers handling business logic:

• Well written software is typically much easier to understand, debug and maintain than SQL stored procedures.
• Application Servers can scale out as well as scale up if the internet application becomes popular.

As a seasoned professional developer, needing a quick fix to improve application latency, the choice can be between moving some slow running business logic into a stored procedure on the database and/or to implement caching of slow processes.

There is however a serious gotcha with database based business logic. If your application needs to scale massively, always prefer systems / processes that can scale out (by this I mean, you can add more servers into the processing pool). SQL Databases can only scale up (you need to find a more powerful server to replace your existing one.) If your application has lots of database business logic, you will reach this problem earlier.

Answer 3

Two very important points are missing in your pro-database arguments:

• performance: database code is executed with direct access to the data, thus avoiding unnecessary transfers (be it across fetching API and mapping schemes on the same machine, or across network for client/server communication)
• consistency: as several applications may access/update the same database, encapsulating consistency and business rules centrally therein, ensures that they will be reliably enforced.

But some very important points are also missing on your contra-database arguments:

• scalability: the more you put on the database, the more this component will become a bottleneck. Of course you can take bigger servers and add CPUs, but sooner or later you'll meet the physical limits.

• vendor lock-in: SQL is very standardized, but the languages for triggers and procedures are rather diversified and often proprietary: T-SQL for Microsoft, PL/SQL for Oracle, any language for DB2. Developping on the database locks you in for a vendor, and doesn't allow you to make benefit of increased competition, or migrate to new operating environments.

• legacy architecture: overcentralisation of data and processing on huge servers... Doesn't this bring us back in the era of mainframes ? This seems obsolete nowadays,when new major architectural trends emerge aiming at maximal scalability: flexible NoSQL databases of different types ideally suited for object oriented development, microservices with every microservice having its own database, and bigdata architecture such as lambda architectures where all the processing pipelines are outside the database.

• obsolete arguments: the time of error prone redundant cobol code copied across applications is over. What could only be reliably encapsulated in an RDBMS yesterday, can very well be encapsulated in modern software architectures, using maintenable and reusable object oriented components, libraries, and version control systems.

To summarize:

• yes, there are valid arguments for putting the maximum of logic on the database side. But these arguments do however no longer meet new needs and constraints of the internet scale, the technological shift, and the emergence of bigdata.
• no, I don't think there is a universal best approach. The most suitable approach shall be choosen by a software architect, case by case, based on the concrete requirements and needs.

Answer 4

Besides all the facts that have been already pointed out, also remember that having business logic in your code rather that the database eventually turns out to be cheaper.

When looking for a developer for an application written in PHP and using MySQL as a database, should your business logic be stored in the database, a simple PHP programmer is not enough, and you will have to find someone who also knows how to write, debug and optimize stored procedures. Suddenly you need a guy who knows not only one thing, PHP, but two, PHP and MySQL programming.

And do not even think about moving to a higher-performance engine like PostgreSQL, then you also need to hire a guy to transform all stored procedures to PL/SQL.

When having business logic in the code, this is only a matter of writing a new abstraction layer for PostgreSQL and swapping out the dependencies in your application, boom, your application suddenly knows PostgreSQL.

Answer 5

Previous answers give great reasons to why it's easier/better to put logic in application code vs in a database. One exception I'd like to highlight is when using a big data database/tech stack. In this case, many of the disadvantages go away:

• You can write unit tests since it's actual code you wrote that sits in the database.
• You can debug, albeit through unit tests.
• You have version control, since it's code.

And the advantages of having logic in the database become way more important:

• Depending on the amount of data being processes, it might be unreasonable to ship data to your application code.
• Scaling - your code scales same as the database scales - in many cases, performance and storage are linear in the number of nodes (machines).

Answer 6

Great question - this is something I advocate at the office very regularly.

My view is that most of the logic should be in code. It's always very tempting to use a variety of languages because they each have their strengths, but unless you have a perfect development setup (which is very rare), it's preferable to stick to one language.

People have mentionned unit testing / revision control versionning, but a very important thing is deployment. Having to synchronize database code changes with code changes can be hazardous.

If you're in a large-scale software development company, you may have enough specialized people who know either side (database programming vs coding), but otherwise it's tricky to find people who can juggle between the 2 worlds (and most importantly, who make the right trade offs when it comes to deciding what part of the logic needs to be in what language).

Personally, I find SQL programming languages very primitive, and development tools for them even worse. So I would favor modern programming languages. A good ORM can save most developers from knowing anything about the database, and is worth investing into. People mentionned the efficiency of doing things server side, and this should not be abandoned. There are some very nice programming patterns that allow to express server-side operations using what appears to be a client-side API (e.g. IQueryable in C#).

In practice, I am still using the odd views or stored procedures, but they are usually mostly purely for aggregation, and have no 'business' logic in them. This is quite useful as they can be used as sources for excel pivottables for instance.