1

I hate to ask the classic question of "business logic in database vs code" again, but I need some concrete reasons to convince an older team of developers that business logic in code is better, because it's more maintainable, above all else. I used to have a lot of business logic in the DB, because I believed it was the single point of access. Maintenance is easy, if I was the only one doing the changing it. In my experience, the problems came when the projects got larger and complicated. Source Control for DB Stored Procs are not so advanced as the ones for newer IDEs, nor are the editors. Business logic in code can scale much better than in the DB, is what I've found in my recent experience.

So, just searching around stackoverflow, I found quite the opposite philosophy from its esteemed members:

https://stackoverflow.com/search?q=business+logic+in+database

I know there is no absolute for any situation, but for a given asp.net solution, which will use either sql server or oracle, for a not a particularly high traffic site, why would I put the logic in the DB?

Community
  • 1
  • 1
M.R.
  • 4,737
  • 3
  • 37
  • 81

3 Answers3

6

Depends on what you call business.

The database should do what is expected.

If the consumers and providers of data expect the database to make certain guarantees, then it needs to be done in the database.

Some people don't use referential integrity in their databases and expect the other parts of the system to manage that. Some people access tables in the database directly.

I feel that from a systems and component perspective, the database is like any other service or class/object. It needs to protect its perimeter, hide its implementation details and provide guarantees of integrity, from low-level integrity up to a certain level, which may be considered "business".

Good ways to do this are referential integrity, stored procedures, triggers (where necessary), views, hiding base tables, etc., etc.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • thanks for your answer -though, when I mean business logic, I'm referring more to very specific business logic, such as 'is this employee allowed to add expenses for this department'... referential integrity and triggers may help me protect orphaned data and preserve foreign keys, but it won't help me do checks like I mentioned. Where should something like that be? – M.R. Aug 10 '11 at 03:17
  • 1
    @Cade I totally agree with you except that if you hide too much or do too much Views and Sprocs can easily grow to be almost unmanagable. I've seen refactoring cases where there's views on top of views and stored procedures used to manage just about everything. Good design could possibly prevent a lot of this though which is why 3-tier and n-tier architecture was invented. To try and avoid a lot of these problems. – Jesus Ramos Aug 10 '11 at 03:19
  • 1
    @M.R. Your case about employees and departments may very well be a case for outside the database. On the other hand, if it is possible that multiple apps could be performing insertion (for instance orders coming in from SSIS packages, with a web front end and a Windows service), it's quite possible you may want the database to enforce it - or force all users through some kind of application server or web service. – Cade Roux Aug 10 '11 at 03:22
  • @Jesus Ramos Having any app be able to see tables prevents you from being able to refactor the database and also makes it difficult to manage the database surface area exposed to apps, since you have to search for more than just consumers of a proc, but all mentions of a table in any app. Like you said, a layer which reduces that surface area which all apps are required to go through (like a web service or an application server) can help. On the other hand, the database can perform that layer just as well in small-medium scenarios and with fewer moving parts. – Cade Roux Aug 10 '11 at 03:25
  • @Cade in some cases that's true. Views are nice for this encapsulation and it doesn't add any business logic (sometimes). The issues I see is when you use sprocs (biggest culprit) that has validation logic for things that could easily be checked before you send the data to the DB – Jesus Ramos Aug 10 '11 at 03:28
  • @Jesus Ramos I tend to do all my validation at the table level with constraints. If it can't be done there, then the proc is a good place IF all inserts are guaranteed to go through the proc. Otherwise a trigger with a RAISERROR might be a candidate. If inserts cannot be guaranteed to go through the proc, then exception reporting is the only way to handle it. You certainly can't trust app(s) to all handle it consistently, so if the database is to make any claims about the integrity of the data (at the higher level sense of integrity), then it has to be done asynchronously with a scheduled job. – Cade Roux Aug 10 '11 at 03:33
  • @Cade that's really the times when you can't trust frameworks (if written across multiple langauges for the same DB). Basic checks are usually what I go for. For complicated business logic (I've seen a company do their tax for payroll in the db which was horrendous as it changed quite often depending on state and such) I really think that belongs in the business logic layer. It just becomes too hard to refactor sometimes but this again is all subjective since in some cases it works, in others it may not. – Jesus Ramos Aug 10 '11 at 03:37
  • @Cade - but what about code resuse? Stored procs can be constructed to be reused, and called from other stored procs, but their is no inheritance, interfaces or any other OOP concepts that help to build a well structered code base. SQL is a procedural language, and not suitable for complicated logic (IMHO), but I'm sure with enough SQL, you can make it do anything - the question is, is it a good idea? In your example, I would architect it so that somehow all the different applications would still go through the same data layer - and the data layer itself can be abstracted... – M.R. Aug 10 '11 at 03:44
1

Database does data things, why weigh down something that is already getting hit pretty hard to give you data. It's a performance thing and a code thing. It's MUCH easier to maintain business logic code than to store it all in the database. Sprocs, Views and Functions can only go so far until you have Views of Views of Views with sprocs to fill that mess in. With business logic you separate your worries. If you have a bug that's causing something to be calculated wrong it's easier to check the business logic code than go into the DB and see if someone messed up something in a Stored Procedure. This is highly opinionated and in some cases it's OK to put some logic in the database but my thoughts on this are it's a database not a logicbase, put things where they belong.

P.S: Might be catchin some heat for this post, it's highly opinionated and other than performance numbers there's no real evidence for either and it becomes a case of what you're working with.

EDIT: Something that Cade mentioned that I forgot. Refrential integrity. By all means please have correct data integrity in your DB, no orphaned records ON DELETE CASCADE's, checks and whatnot.

Jesus Ramos
  • 22,940
  • 10
  • 58
  • 88
  • yeah, I know :). The whole 'business logic in DB' is an older philosophy (or so I thought) when DBs performed MUCH faster than frameworks. Todays frameworks are so much better, faster, more flexible and more scalable, so I didn't think this philosphy prevailed. But my search on stackoverflow seems to indicate otherwise.... – M.R. Aug 10 '11 at 03:12
  • @M.R. I'm a little shocked at that myself. I've done perf tests at many places I've worked to discourage this practice as it's bogging down the database to do a lot of the logic there. .NET framework (very popular now) is very fast as is PHP which are very commonly used with DB backends. No reason to really dump more work onto the database (at least I think so). – Jesus Ramos Aug 10 '11 at 03:17
  • 3
    I would also note that many "developers" are not actually very good at writing SQL. Hence the "logic" they put in the database may perform poorly, and they blame this on the database instead of on the techniques they use. Overuse of triggers, almost any use of cursors, inefficient queries due to misunderstanding sargability, and basic indexing strategies all play a part here. – Cade Roux Aug 10 '11 at 03:29
  • @Cade a lot of those mostly play on the data end and you're absolutely right about some developers and their SQL skills. Which is why it's so tempting to have a DBA just write all the SQL on the DB and use that instead. – Jesus Ramos Aug 10 '11 at 03:31
  • 1
    @Jesus Ramos As long as the DBA has systems architecture experience. They can often be just as closed minded about making things work right for developers. – Cade Roux Aug 10 '11 at 03:34
-1

I have faced with database logic on one of huge projects. This was caused by the decision of main manager who was the DBA specialist. He said that the application should be leightweight, it should know nothing about database scheme, joined tables, etc, and anyway stored Procs executes much faster than the transaction scopes and queries from client. At the other side, we had too much bugs with database object mappings (stored prod or view based on view based on other view etc). It was unreachable to understand what is happening with our data because of each button clicked called a huge stored proc with 70-90-120 parameters and updated several (10-15) tables. We had no ability to query simple select request so we had to compile a view or stored Proc and class in code for this just for one simple join :-( of course when the table or view definition changes you should recompile all other dB objects based on edited object elsewhere you will get runtime Exception. So I think that logic in database is a horrible way. Of course you can store some pieces of code in stored procs if needed by performance or security issues, but you shoul not develop everything in the Database) the logic should be flexible, testable and maintenable, and you can not reach this points using database for storing logic)

Mihail
  • 11
  • 2