Where ORMs blur the lines between code and data, how do you decide what logic should be a stored procedure, and what should be coded?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.