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?
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?
design patterns;database design;orm
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 juniorThe 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 guysYou don't want to be tied to a certain brand of databaseYour DBA's are more like sysadmins than programmers - focused on keeping the database running rather than on application designYou 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 SummaryThere 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. DataImplementing 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.