Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Take the following pseudocode:

CreateInvoiceAndCalculate(ItemsAndQuantities, DispatchAddress, User);

And say CreateInvoice does the following:

  • Create a new entry in an Invoices table belonging to the specified User to be sent to the given DispatchAddress.
  • Create a new entry in an InvoiceItems table for each of the items in ItemsAndQuantities, storing the Item, the Quantity, and the cost of the item as of now (by looking it up from an Items table)
  • Calculate the total amount of the invoice (e.g., shipping and taxes) and store it in the new Invoice row.

At a glance you wouldn't be able to tell if this was a method in my application's code, or a stored procedure in the database that is being exposed as a function by the ORM. And to some extent it doesn't really matter.

Now technically none of this is business logic. You're not making any decisions - just performing a calculation and creating records. However some may argue that because you are performing a calculation that affects the business (the total amount to be invoiced) that this isn't something that should be done in a stored procedure and instead should be in code.

So for this specific example - why would it be more appropriate to do one or the other? And where do you draw the line? Or does it even particularly matter as long as it's sufficiently well documented?

share|improve this question
1  
I would say it has too many responsibilities.. –  Simon Whitehead Oct 24 '12 at 10:49
1  
I don't understand why ORMs are relevant to this question. –  pdr Oct 24 '12 at 10:59
1  
@pdr - because in ORMs, stored procedures often look like functions the same as they would as if the functionality was written in code. Hence my comment about the lines being blurred because if you treat it as a black box, there's suddenly no difference. Updated the question to clarify this. –  PhonicUK Oct 24 '12 at 11:00
4  
When I see "And" in a function name this raises a red flag for me. –  jfrankcarr Oct 24 '12 at 11:26
1  
@PhonicUK - I would create a new instance of the CalculatedInvoice class that was a subclass of the Invoice class. Total amount would be a calculated read-only property based on the LineItem objects values. Typically, I don't see the total invoice value stored, only calculated, since this prevents errors or intentional manipulations. –  jfrankcarr Oct 24 '12 at 16:31
show 1 more comment

4 Answers 4

up vote 6 down vote accepted

I need to take two asides in order to answer your question. #1 is "Should Business Logic be performed in stored procedures, in application code, or both?" #2 is about blurring the lines between code and data. The answer to the first question depends on the environment you work in.

Use Stored Procedures When:

  • You have a crackerjack database team that does the design for your applications and the middle and front-end developers are very junior
  • The logic will be shared by multiple applications whose only commonality is that they use the same database.
  • The number of calls between application and database would make application code too slow and a stored procedure would be faster (and you need it to be that fast).

Don't Use Stored Procedures When:

  • Your application design is done by the middle guys
  • You don't want to be tied to a certain brand of database
  • Your DBA's are more like sysadmins than programmers - focused on keeping the database running rather than on application design
  • You have invested in a Service Oriented Architecture where all your code interacts with a service which in turn calls the database.
  • Your database stored procedure language is from the stone age (e.g. MySQL).
  • You can achieve the necessary performance without them.

Stored Procedure Summary

There has definitely been a move away from stored procedures in recent years. Basically, put the important logic where the best people can take care of it.

Code vs. Data

Implementing a Set as a definition of what belongs in that set instead of as a container that holds things is using code instead of data. Passing functions to other functions treats them like data. Modeling decision-making as a tree structure treats the tree data like code.

Performing processing in the database vs. a web service, vs. the "back end", vs. the "front end", vs. in AJAX in someone's browser is still just code operating on data, albeit in different areas of your infrastructure. I would say rather that it blurs the responsibilities between different layers of your architecture.

Object-Relational Mapping (ORM)

Yes, the goal of ORM is supposed to let you work in your chosen language, temporarily forgetting about the storage format. ORM succeeds when it simplifies the problems of manipulating your abstractions. A procedure that wraps a stored procedure is just another level of abstraction. If you don't need to think about whether or not it's backed by a stored procedure, then ORM is succeeding.

share|improve this answer
1  
Some US companies, due to SOX compliance paranoia, prevent non-DB admins from having direct table access and require using stored procs created by the DBAs. That's another reason why you may end up having to use SP's. –  jfrankcarr Oct 24 '12 at 13:49
    
@jfrankcarr I don't think that SOX compliance is the reason for this, just paranoia about it. Essentially it is all in their head. –  maple_shaft Oct 24 '12 at 14:29
1  
@jfrankcarr - as long as I can run the usp_Update_Employee_Salary proc, compliance can keep their tables ;) –  JeffO Oct 24 '12 at 14:51
    
@maple_shaft - I agree it's paranoia. SOX compliance consultants are very practiced at instilling this level of paranoia into upper management types though. –  jfrankcarr Oct 24 '12 at 16:34
    
@jfrankcarr Of course they would. Has anybody ever hired a management consultant and not expected them to lambast the current processes and procedures? :) –  maple_shaft Oct 24 '12 at 16:52
add comment

Now technically none of this is business logic. You're not making any decisions - just performing a calculation and creating records.

How do you figure that this is not business logic? I would argue that most developers agree that this is the very definition of business logic. It encapsulates an application behavior that performs a service for a user or task to directly or indirectly further a business goal of creating invoices. While simply fetching some data on criteria can be argued is not in fact business logic, calculating new data certainly is.

So where do Stored Procedures come into play? This answer is not so clear and not everybody agrees on the proper role of Stored Procedures. Not everybody even agrees that stored procedures should ever exist, arguing that they fundamentally blue the line between the data tier and business logic which should ideally exist solely in the application layer. Others take the other hard line approach that stored procedures should contain as much business logic as possible and the application tier should be thin.

There is always of course the middle road when it comes to stored procedures. For resource or time intensive tasks where a significant performance advantage can be had by joining records and performing calculations at the database tier, (Eg. analytics, data marts, reports, etc...) then a strong argument can be made for the importance and role of stored procedures in an application.

share|improve this answer
add comment

Stored procedures are horrible places for any kind of business logic, and if an ORM masks a stored procedure this is simply for convenience.

Your entire hypothetical code above can be done without stored procedures, and should be. A database in terms of an ORM should mean that the database doesn't matter any more. It becomes just a persistence layer. A stored procedure should only be made if there is no other alternative to a specific problem.

This is of course a strict approach, but my experience in trying to work with both did nothing but frustrate me. I ended up moving all the logic to my code. Stored procedures shield you from the underlying data model, ORM exposes the model to you. I Just think they are two fundamentally different ways of working.

It's also easier and less ambiguous when you take a stricter approach to the problem.

share|improve this answer
add comment

Based on your comment:

because in ORMs, stored procedures often look like functions the same as they would as if the functionality was written in code. Hence my comment about the lines being blurred because if you treat it as a black box, there's suddenly no difference

then yes, if someone else had to write the database access part of your application and all you had to deal with was the result, they're the same. I would only care if the other dev used an ORM or Stored Proc if I need to work with that code. Maintenance is an issue, as well as, knowing what is going on in the black box.

With an ORM, you know where the code is at. If you use a stored procedure, I don't know if you're manipulating the results in the proc itself or if your data access layer is doing something else with it.

ORM's blur the line between code and data

I would say code and databases And using the word "blur" has a negative connotation here. As if you're unsure what is going on. Blurring bothers me; putting it in a black box is the opposite of a distraction (assuming I know what was put in the black box which if that's the point of the question is just a matter of understanding the code used.). Out of site, out of mind.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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