5

For the sake of argument:

  • Let's assume the application we are building is an amortization schedule.
  • Let's also assume that the database has a table called tblAmortizationPayments that stores the information about each monthly payment in a separate row.
  • Let's further assume that the database language can support the math calculations needed to perform the calculations.

If you were to build this amortization schedule for a 30 year mortgage, how would you decide between placing the code for performing these 360 calculations inside the database (I'm assuming this would be a stored procedure) or inside the application?

I'm more interested in your thought process for making this type of decision or any similar scenario that might come along.

4
  • 8
    Ask an "Oracle developer" or a "DB2 developer", and they will tell you that anything that could possibly be put into the database should be put there. Ask a "Java developer" or a ".Net developer" and they will tell you that all logic belongs in a business layer written in Java or .Net. You're getting more of the first kind of answer here because you've tagged the question with "database". Sep 16, 2011 at 23:43
  • 2
    Are you de-normalizing the database, by storing both the inputs to a calculation, and the output of the calculation? Sep 17, 2011 at 0:15
  • @Kevin - I'm not sure what you mean by that statement. Sep 17, 2011 at 0:36
  • 1
    There are many similar questions e.g. programmers.stackexchange.com/questions/107644/… and stackoverflow.com/questions/532875/… Sep 17, 2011 at 8:25

8 Answers 8

3

The problem with putting the code inside the database is that the database's programming language isn't very good, relative to what you write the business logic in. Lets say that after a while you want to support multiple types of amortizations based on the type of contract, and that some contracts are based on variable rates calculated as formulas defined differently by individual contract with variables in the rate formulas referencing rate tables such as LIBOR. Now imagine multiple currencies are thrown in. Now imagine some contracts calculate interest on the first business day of the month based on the holiday calendar of the country the contract is written in ...

Obviously, YAGNI will probably cover all my examples, but the point is that there are lots of business requirements that might make you want to move business logic out of the database and into a more powerful language. Of course, you could apply YAGNI and keep it in a stored procedure now, but there is a higher chance that it will move to the business layer than there is of it moving to the database if it starts out in the business layer, as a general rule.

This is the kind of thing that can be a tough call, but it's something to keep in mind in your particular case.

5
  • 1
    -1: Some database engines, e.g. Oracle and Postgres, allow stored procedures to be written in languages other than SQL. Sep 17, 2011 at 0:07
  • @kevin cline- aware of that. Didn't think that's what he meant but it's possible he did - the question is pretty brief.
    – psr
    Sep 17, 2011 at 0:25
  • @psr - I was only thinking in terms of the SQL capabilities of the database language and not any external extensions of that language. Sep 17, 2011 at 0:39
  • +1: It's true that the development environment for stored procedures is spartan compared to most IDE's Sep 17, 2011 at 17:54
  • I asked this question over 11 years ago. Since then I have come to appreciate a database as the place to store results not produce results. If I was giving advice to myself, I'd tell myself to store the results in the database and do the programming outside the database.. Feb 18, 2023 at 22:29
8

You will get two schools of thought: Stored Procedures and No Stored Procedures. I'm in the camp that the database is just for holding my data, my program is for analyzing and using that data. With the advent of the Entity Framework, it pushes more of the emphasis on the code side (in my opinion).

If using Stored Procedures, then you have TWO places to look for a potential bug: your code, and/or your Stored Procedure.

2
  • True, In general I try to keep stored procedure logic limited to insert, updates, and deletes. The DAL layer is ignorant, just give me data and I will update it, I don't perform any logic.
    – Jon Raynor
    Sep 17, 2011 at 0:04
  • True, although I do acknowledge the occasional exception however. For instance, high performance logic sometimes makes sense at the database level minimizing overhead, back and forth database communication that may occur between the Database and the Middle Tier.
    – maple_shaft
    Sep 17, 2011 at 3:57
3

It is always advisable to keep database logic inside the database. The reason is because the abstraction of the details of the underlying datastore should be made opaque to the application.

If the database structure changes, you will want to alter stored procs, functions, and views. But you don't want the application to break from any minor to most major database changes.

The database engine, through the use of stored procedures, also caches execution plans even if the paramter values change.

Logic separation should be separated as much as possible, and that's all part of n-tier architecture.

Leave database logic to the database.

5
  • 7
    Except that calculating mortgage payment schedules is not "database logic" by any stretch of imagination. Sep 16, 2011 at 23:40
  • That could very well be handled in the database. Mortgage information is most likely stored there, as well as user records. Why can't that be database logic? It all depends on his initial design.
    – user29981
    Sep 17, 2011 at 0:07
  • 4
    "could be" does not mean "should be". What exactly is your definition of "database logic"? Mine is "logic that is directly concerned with the structure and integrity of the data". Which calculating payment schedules is not; it's clearly business logic. Sep 17, 2011 at 0:41
  • 2
    @Surfer513. Logic in the DB to avoid breaking the app? When code is in the DB it BECOMES an app and code will have to change when the DB changes. The logic of putting code there to avoid application change is faulty. I think the reason for putting logic in the DB should be more about avoiding network round trips, and easy use of set-based operations. Putting logic outside the DB may be for easier horizontal scaling, access to better libraries and languages to handle the problem at hand. For a heavy algorithmic/mathematical problem this is important. Not so much for a basic CRUD app.
    – jojo
    Sep 17, 2011 at 3:14
  • @jojo - Make this an answer and you get my vote. Sep 17, 2011 at 12:39
2

The main factors to my mind:

  • Does the developer that will program this have more proficiency in the language of the database or the language of the application?
  • How loaded is the database server compared to the server running the application?
  • How much code is currently existing in either form?

If the DB server is getting used so heavily that it may time out in running the stored procedure then it may be better to do it in the application. Similarly, if the developer has extensive experience in one over the other then either may make sense as it could be used to build up skills in the weaker side or leverage the stronger side depending on which way one wants to value the upside of each option.

1
  • 1
    Good points; especially the second - the DB is much more likely to be a bottleneck than an appserver, since DB clustering is more difficult (and expensive) than having multiple app servers. Sep 16, 2011 at 23:45
1

There are two competing philosophies here:

If you place all of the business logic for calculating the payments in the application, then any further application needs that come along (desktop, web, smartphone, changing platforms, web service, other databases, etc.) need to have that code duplicated. We struggle with that now at my work with two applications that access the same databases, do some of the same things, and yet don't always rely on streamlined database code.

If you have the database do the calculations, then it only needs to be done in one place. Any application can connect to the database, call the stored procedure and get the same results. You'll never have two execs arguing that their reports are giving the right numbers, or someone's house getting foreclosed on because one app shows they've been shorting their payments and another shows they've been overpaying.

My opinion is that the business logic should be done as much as possible in the database, but if you can't then make sure you only put it in one place and not split it between the database and the application.

3
  • 3
    This is the reason why it's becoming less and less common to have a database shared between applications. Integration instead happens at a higher level, SOA-like. Sep 16, 2011 at 23:38
  • 1
    you could have webservices. (or non-webservices for max performance). This will give centralized logic for all the client applications. And ability to scale horizontally without duplicating databases to boot.
    – jojo
    Sep 17, 2011 at 3:25
  • 2
    I'm a bit baffled by your "code duplication" problem; that's what libraries are for. Put the code in a common DLL and distribute that DLL amongst your applications. Sep 17, 2011 at 17:53
0

There is no right answer for this.

Stored Procedures will be easier to update if there is a bug, just update the procedure, no need to do a code build. With code a build would be required if there is a bug.

If you ever change your backend data (Oracle to SQl Server or vice versa), the code will need to be ported over, with code the logic stays intact and can be ported, provided platform remain the same.

With code, it should be easier and less complex to design an extendable amortization scheduler. If the calculations change from client, extensibility would be design goals. Stored procedures are somewhat monolithic, so code would be a better choice. With a stored procedure I could see it becoming messy if different types of calculations are required.

If the business says the calcuation is the same no matter what, I would lean toward a stored procedure, if there could be future differences, I'd put it in code and make it extensible.

4
  • 2
    If avoiding code builds is an important factor in your basic architecture decisions, there is something very, very wrong with your build process. Sep 17, 2011 at 0:44
  • @Micheal - From a deployment prospective it makes a difference, its not a factor in the build process, sorry if you misinterpreted my intention. For example, say a client has a 10,000 seat deployment, its much easier for the client to take a SQL script then to roll out a new build.
    – Jon Raynor
    Sep 17, 2011 at 0:57
  • If you have a piece of software deployed on 10,000 machines, you need some sort of automated deployment infrastructure anyway, and this kind of thing can be considered a solved problem nowadays, where even text editors have an autoupdate function. So no, it should not be a big difference either. Sep 17, 2011 at 1:05
  • Updating 10,000 client machines is a big problem. They are not gonna all update at the same time. Also it's possible to manipulate the machine code to by-pass logic. Services, services, services. Central logic. 1 update deployment. Viola!
    – jojo
    Sep 17, 2011 at 3:28
0

Types of calculations. In your example, I don't think databases do as good of a job at this type of task as a procedural program would.

Sharing Code. There may be limitations on how coding can be shared. If you're writing custom code for all applications, you have any reusable format you wish. Some third party applications or any app where you don't control the code and report writers may require you to use a stored procedure or some other type of database object.

Spreading the work among team members. Putting code in the same place and being consistent is great unless you have a two person staff and one of them is in the database area of expertise, it may make sense to split the work to utilize the man-hours.

Spreading the work among hardware. This really only makes sense if there are lenghtly batch processes going on. It would drive developers nuts trying to load balance random tasks between app and database servers. You're more likely to just increase the hardware on one or the other and put all your code there.

Developer Expertise Developers have preferences that are probably based on proficiency. You'll code it where you think it is easiest. Of course you're trying to work within the parameters of the application, but if given the choice...

0

The first thing you have to ask yourself is how much you care about the accuracy and correctness of the calculation as it is recorded in the database.

If money or statutory privacy requirements are on the line you simply cannot trust the application with the calculation. Either the database has to do the calculation, or preferably, some intermediate business logic tier running in a locked room under your control.

The problem is that you can't trust data coming from an external client! If somebody can make money by lying to your database there is a good chance they will. If I could arrange to get a $500,000 mortgage at 1% by simply reporting the wrong number to your database, then I have a strong motivation to do just that. For that kind of money it might be worth my while to run your application under a debugger while watching the traffic between the applications and the database with network sniffers. Eventually I'll learn how to impersonate your application, and send the database a number of my own choosing.

If you don't really care about the integrity of the calculation and nobody can gain any advantage by lying about calculation then you don't have to worry so much. You can decide based on the reasoning other folks have offered.

EDIT: Let me add a couple of specific example where you should not trust a calculation coming from the client.

  1. Security validation. As silly as it sounds, this crops up on web sites from time to time. The password checking is done in embedded Javascript, or the user is validated once by checking with the database, and the result of the validation is stored in a cookie or as a parameter in the URL. I kid you not, there have been web sites that have been cracked simply by changing a URL parameter from ?isAuthenticated="F" to ?isAuthenticated="T".

  2. Bonus/commission calculations. Suppose you have an application used by the sale force where they enter their sales, and their bonus is calculated. If the database simply records the bonus or commission as submitted by the application, then a sales person with a technical background could use a debugger to inflate his commissions. The database or the middle tier at least has to check the validity of the calculation, and then you are duplicating the code for the calculation in the database and the application.

2
  • 1
    You're claiming that all applications that interact with databases are not secured? Some how the 1% or 10% has to be put into a formula regardless of where it is calculated. It's called security.
    – JeffO
    Sep 17, 2011 at 8:23
  • @Jeff, if you can run the app under a debugger you can make it tell the database anything you want. Put a breakpoint where the calculation return, then change the return value when the break point fires. Whether this is a concern or not depends on what the formula is used for. Sep 17, 2011 at 16:34

Not the answer you're looking for? Browse other questions tagged or ask your own question.