[ACCEPTED]-Business Logic: Database or Application Layer-business-logic

Maintainability of your code is always a 16 big concern when determining where business 15 logic should go.

Integrated debugging tools 14 and more powerful IDEs generally make maintaining 13 middle tier code easier than the same code 12 in a stored procedure. Unless there is 11 a real reason otherwise, you should start 10 with business logic in your middle tier/application 9 and not in stored procedures.

However when 8 you come to reporting and data mining/searching, stored 7 procedures can often a better choice. This 6 is thanks to the power of the databases 5 aggregation/filtering capabilities and the 4 fact you are keeping processing very close 3 the the source of the data. But this may 2 not be what most consider classic business 1 logic anyway.

Put enough of the business logic in the 4 database to ensure that the data is consistent 3 and correct.

But don't fear having to duplicate 2 some of this logic at another level to enhance 1 the user experience.

For very simple cases you can put your business 35 logic in stored procedures. Usually even 34 the simple cases tend to get complicated 33 over time. Here are the reasons I don't 32 put business logic in the database:

Putting 31 the business logic in the database tightly 30 couples it to the technical implementation 29 of the database. Changing a table will cause 28 you to change a lot of the stored procedures 27 again causing a lot of extra bugs and extra 26 testing.

Usually the UI depends on business 25 logic for things like validation. Putting 24 these things in the database will cause 23 tight coupling between the database and 22 the UI or in different cases duplicates 21 the validation logic between those two.

It 20 will get hard to have multiple applications 19 work on the same database. Changes for one 18 aplication will cause others to break. This 17 can quickly turn into a maintenance nightmare. So 16 it doesn't really scale.

More practically 15 SQL isn't a good language to implement business 14 logic in an understandable way. SQL is great 13 for set based operations but it misses constructs 12 for "programming in the large" it's hard 11 to maintain big amounts of stored procedures. Modern 10 OO languages are better suited and more 9 flexible for this.

This doesn't mean you 8 can't use stored procs and views. I think 7 it sometimes is a good idea to put an extra 6 layer of stored procedures and views between 5 the tables and application(s) to decouple 4 the two. That way you can change the layout 3 of the database without changing external 2 interface allowing you to refactor the database 1 independently.

It's really up to you, as long as you're consistent.

One good reason 9 to put it in your database layer: if you 8 are fairly sure that your clients will never 7 ever change their database back-end.

One 6 good reason to put it in the application 5 layer: if you are targeting multiple persistence 4 technologies for your application.

You should 3 also take into account core competencies. Are 2 your developers mainly application layer 1 developers, or are they primarily DBA-types?

While there are certainly benefits to have 8 the business logic on the application layer, I'd 7 like to point out that the languages/frameworks 6 seem to change more frequently then the 5 databases.

Some of the systems that I support, went 4 through the following UIs in the last 10-15 3 years: Oracle Forms/Visual Basic/Perl CGI/ ASP/Java 2 Servlet. The one thing that didn't change 1 - the relational database and stored procedures.

While there is no one right answer - it 23 depends on the project in question, I would 22 recommend the approach advocated in "Domain Driven Design" by 21 Eric Evans. In this approach the business 20 logic is isolated in its own layer - the 19 domain layer - which sits on top of the 18 infrastructure layer(s) - which could include 17 your database code, and below the application 16 layer, which sends the requests into the 15 domain layer for fulfilment and listens 14 for confirmation of their completion, effectively 13 driving the application.

This way, the business 12 logic is captured in a model which can be 11 discussed with those who understand the 10 business aside from technical issues, and 9 it should make it easier to isolate changes 8 in the business rules themselves, the technical 7 implementation issues, and the flow of the 6 application which interacts with the business 5 (domain) model.

I recommend reading the above 4 book if you get the chance as it is quite 3 good at explaining how this pure ideal can 2 actually be approximated in the real world 1 of real code and projects.

Anything that affects data integrity must 12 be put at the database level. Other things 11 besides the user interface often put data 10 into, update or delete data from the database 9 including imports, mass updates to change 8 a pricing scheme, hot fixes, etc. If you 7 need to ensure the rules are always followed, put 6 the logic in defaults and triggers.

This 5 is not to say that it isn't a good idea 4 to also have it in the user interface (why 3 bother sending information that the database 2 won't accept), but to ignore these things 1 in the database is to court disaster.

Database independence, which the questioner 27 rules out as a consideration in this case, is 26 the strongest argument for taking logic 25 out of the database. The strongest argument 24 for database independence is for the ability 23 to sell software to companies with their 22 own preference for a database backend.

Therefore, I'd 21 consider the major argument for taking stored 20 procedures out of the database to be a commercial 19 one only, not a technical one. There may 18 be technical reasons but there are also 17 technical reasons for keeping it in there 16 -- performance, integrity, and the ability 15 to allow multiple applications to use the 14 same API for example.

Whether or not to use 13 SP's is also strongly influenced by the 12 database that you are going to use. If you 11 take database independence out of consideration 10 then you're going to have very different 9 experiences using T-SQL or using PL/SQL.

If 8 you are using Oracle to develop an application 7 then PL/SQL is an obvious choice as a language. It's 6 is very tightly coupled with the data, continually 5 improved in every relase, and any decent 4 development tool is going to integratePL/SQL 3 development with CVS or Subversion or somesuch.

Oracle's 2 web-based Application Express development 1 environment is even built 100% with PL/SQL.

If you need database independence, you'll 27 probably want to put all your business logic 26 in the application layer since the standards 25 available in the application tier are far 24 more prevalent than those available to the 23 database tier.

However, if database independence 22 isn't the #1 factor and the skill-set of 21 your team includes strong database skills, then 20 putting the business logic in the database 19 may prove to be the best solution. You can 18 have your application folks doing application-specific 17 things and your database folks making sure 16 all the queries fly.

Of course, there's a 15 big difference between being able to throw 14 a SQL statement together and having "strong 13 database skills" - if your team is closer 12 to the former than the latter then put the 11 logic in the application using one of the 10 Hibernates of this world (or change your 9 team!).

In my experience, in an Enterprise 8 environment you'll have a single target 7 database and skills in this area - in this 6 case put everything you can in the database. If 5 you're in the business of selling software, the 4 database license costs will make database 3 independence the biggest factor and you'll 2 be implementing everything you can in the 1 application tier.

Hope that helps.

It is nowadays possible to submit to subversion 13 your stored proc code and to debug this 12 code with good tool support.

If you use 11 stored procs that combine sql statements 10 you can reduce the amount of data traffic 9 between the application and the database 8 and reduce the number of database calls 7 and gain big performance gains.

Once we 6 started building in C# we made the decision 5 not to use stored procs but now we are moving 4 more and more code to stored procs. Especially 3 batch processing.

However don't use triggers, use 2 stored procs or better packages. Triggers 1 do decrease maintainability.

Putting the code in the application layer 4 will result in a DB independent application.

Sometimes 3 it is better to use stored procedures for 2 performance reasons.

It (as usual) depends 1 on the application requirements.

The only thing that goes in a database is 42 data.

Stored procedures are a maintenance 41 nightmare. They aren't data and they don't 40 belong in the database. The endless coordination 39 between developers and DBA's is little more 38 than organizational friction.

It's hard to 37 keep good version control over stored procedures. The 36 code outside the database is really easy 35 to install -- when you think you've got 34 the wrong version you just do an SVN UP 33 (maybe an install) and your application's 32 back to a known state. You have environment 31 variables, directory links, and lots of 30 environment control over the application.

You 29 can, with simple PATH manipulations, have variant 28 software available for different situations 27 (training, test, QA, production, customer-specific 26 enhancements, etc., etc.)

The code inside 25 the database, however, is much harder to 24 manage. There's no proper environment -- no 23 "PATH", directory links or other environment 22 variables -- to provide any usable control 21 over what software's being used; you have 20 a permanent, globally bound set of application 19 software stuck in the database, married 18 to the data.

Triggers are even worse. They're 17 both a maintenance and a debugging nightmare. I 16 don't see what problem they solve; they 15 seem to be a way of working around badly-designed 14 applications where someone couldn't be bothered 13 to use the available classes (or function 12 libraries) correctly.

While some folks find 11 the performance argument compelling, I still 10 haven't seen enough benchmark data to convince 9 me that stored procedures are all that fast. Everyone 8 has an anecdote, but no one has side-by-side 7 code where the algorithms are more-or-less 6 the same.

[In the examples I've seen, the 5 old application was a poorly designed mess; when 4 the stored procedures were written, the 3 application was re-architected. I think 2 the design change had more impact than the 1 platform change.]

The business logic should be placed in the 17 application/middle tier as a first choice. That 16 way it can be expressed in the form of a 15 domain model, be placed in source control, be 14 split or combined with related code (refactored), etc. It 13 also gives you some database vendor independence.

Object 12 Oriented languages are also much more expressive 11 than stored procedures, allowing you to 10 better and more easily describe in code 9 what should be happening.

The only good reasons 8 to place code in stored procedures are: if 7 doing so produces a significant and necessary 6 performance benefit or if the same business 5 code needs to be executed by multiple platforms 4 (Java, C#, PHP). Even when using multiple 3 platforms, there are alternatives such as 2 web-services that might be better suited 1 to sharing functionality.

The answer in my experience lies somewhere 20 on a spectrum of values usually determined 19 by where your organization's skills lie.

The 18 DBMS is a very powerful beast, which means 17 proper or improper treatment will bring 16 great benefit or great danger. Sadly, in 15 too many organizations, primary attention 14 is paid to programming staff; dbms skills, especially 13 query development skills (as opposed to 12 administrative) are neglected. Which is 11 exacerbated by the fact that the ability 10 to evaluate dbms skills is also probably 9 missing.

And there are few programmers who 8 sufficiently understand what they don't 7 understand about databases.

Hence the popularity 6 of suboptimal concepts, such as Active Records 5 and LINQ (to throw in some obvious bias). But 4 they are probably the best answer for such 3 organizations.

However, note that highly 2 scaled organizations tend to pay a lot more 1 attention to effective use of the datastore.

There is no standalone right answer to this 4 question. It depends on the requirements 3 of your app, the preferences and skills 2 of your developers, and the phase of the 1 moon.

Business logic is to be put in the application 13 tier and not in the database. The reason 12 is that a database stored procedure is always 11 dependen on the database product you use. This 10 break one of the advantages of the three 9 tier model. You cannot easily change to 8 an other database unless you provide an 7 extra stored procedure for this database 6 product. on the other hand sometimes, it 5 makes sense to put logic into a stored procedure 4 for performance optimization.

What I want 3 to say is business logic is to be put into 2 the application tier, but there are exceptions 1 (mainly performance reasons)

Bussiness application 'layers' are:

1. User Interface

This 25 implements the business-user's view of h(is/er) job. It 24 uses terms that the user is familiar with.

2. Processing

This 23 is where calculations and data manipulation 22 happen. Any business logic that involves 21 changing data are implemented here.

3. Database

This 20 could be: a normalized sequential database 19 (the standard SQL-based DBMS's); an OO-database, storing 18 objects wrapping the business-data; etc.

What goes Where

In 17 getting to the above layers you need to 16 do the necessary analysis and design. This 15 would indicate where business logic would 14 best be implemented: data-integrity rules 13 and concurrency/real-time issues regarding 12 data-updates would normally be implemented 11 as close to the data as possible, same as 10 would calculated fields, and this is a good 9 pointer to stored-procedures/triggers, where 8 data-integrity and transaction-control is 7 absolutely necessary.

The business-rules 6 involving the meaning and use of the data 5 would for the most part be implemented in 4 the Processing layer, but would also appear 3 in the User-Interface as the user's work-flow 2 - linking the various process in some sequence 1 that reflects the user's job.

Imho. there are two conflicting concerns 64 with deciding where business logic goes 63 in a relational database-driven app:

• maintainability
• reliability

Re. maintainability:  To 62 allow for efficient future development, business 61 logic belongs in the part of your application 60 that's easiest to debug and version control.

Re. reliability:  When 59 there's significant risk of inconsistency, business 58 logic belongs in the database layer.  Relational 57 databases can be designed to check for constraints 56 on data, e.g. not allowing NULL values in 55 specific columns, etc.  When a scenario 54 arises in your application design where 53 some data needs to be in a specific state 52 which is too complex to express with these 51 simple constraints, it can make sense to 50 use a trigger or something similar in the 49 database layer.

Triggers are a pain to keep 48 up to date, especially when your app is 47 supposed to run on client systems you don't 46 even have access too.  But that doesn't 45 mean it's impossible to keep track of them 44 or update them.  S.Lott's arguments in his 43 answer that it's a pain and a hassle are 42 completely valid, I'll second that and have 41 been there too.  But if you keep those limitations 40 in mind when you first design your data 39 layer and refrain from using triggers and 38 functions for anything but the absolute 37 necessities it's manageable.

In our application, most 36 business logic is contained in the application's 35 model layer, e.g. an invoice knows how to 34 initialize itself from a given sales order.  When 33 a bunch of different things are modified 32 sequentially for a complex set of changes 31 like this, we roll them up in a transaction 30 to maintain consistency, instead of opting 29 for a stored procedure.  Calculation of 28 totals etc. are all done with methods in 27 the model layer.  But when we need to denormalize 26 something for performance or insert data 25 into a 'changes' table used by all clients 24 to figure out which objects they need to 23 expire in their session cache, we use triggers/functions 22 in the database layer to insert a new row 21 and send out a notification (Postgres listen/notify 20 stuff) from this trigger.

After having our 19 app in the field for about a year, used 18 by hundreds of customers every day, the 17 only thing I would change if we were to 16 start from scratch would be to design our 15 system for creating database functions (or 14 stored procedures, however you want to call 13 them) with versioning and updates to them 12 in mind from the get-go.

Thankfully, we do 11 have some system in place to keep track 10 of schema versions, so we built something 9 on top of that to take care of replacing 8 database functions.  It would've saved us 7 some time now if we'd considered the need 6 to replace them from the beginning though.

Of 5 course, everything changes when you step 4 outside of the realm of RDBMS's into tuple-storage 3 systems like Amazon SimpleDB and Google's 2 BigTable.  But that's a different story 1 :)

We put a lot of business logic in stored 5 procedures - it's not ideal, but quite often 4 it's a good balance between performance 3 and reliability.

And we know where it is 2 without having to search through acres of 1 solutions and codebase!

Scalability is also very important factor 5 for pusing business logic in middle or app 4 layer than to database layer.It should be 3 understood that DatabaseLayer is only for 2 interacting with Database not manipulating 1 which is returned to or from database.

I remember reading an article somewhere 7 that pointed out that pretty well everything 6 can be, at some level, part of the business 5 logic, and so the question is meaningless.

I 4 think the example given was the display 3 of an invoice onscreen. The decision to 2 mark an overdue one in red is a business 1 decision...

It's a continuum. IMHO the biggest factor 34 is speed. How can u get this sucker up and 33 running as quickly as possible while still 32 adhering to good tenants of programming 31 such as maintainability, performance, scalability, security, reliability 30 etc. Often times SQL is the most concise 29 way to express something and also happens 28 to be the most performant many times, except 27 for string operations etc, but that's where 26 your CLR Procs can help. My belief is to 25 liberally sprinkle business logic around 24 whereever you feel it is best for the undertaking 23 at hand. If you have a bunch of application 22 developers who shit their pants when looking 21 at SQL then let them use their app logic. If 20 you really want to create a high performance 19 application with large datasets, put as 18 much logic in the DB as you can. Fire your 17 DBA's and give developers ultimate freedom 16 over their Dev databases. There is no one 15 answer or best tool for the job. You have 14 multiple tools so become expert at all levels 13 of the application and you'll soon find 12 that you're spending a lot more time writing 11 nice consise expressive SQL where warranted 10 and using the application layer other times. To 9 me, ultimately, reducing the number of lines 8 of code is what leads to simplicity. We 7 have just converted a sql rich application 6 with a mere 2500 lines of app code and 1000 5 lines of SQL to a domain model which now 4 has 15500 lines of app code and 2500 lines 3 of SQL to achieve what the former sql rich 2 app did. If you can justify a 6 fold increase 1 in code as "simplified" then go right ahead.

This is a great question! I found this 33 after I had already asked a simliar question, but 32 this is more specific. It came up as a 31 result of a design change decision that 30 I wasn't involved in making.

Basically, what 29 I was told was that If you have millions 28 of rows of data in your database tables, then 27 look at putting business logic into stored 26 procedures and triggers. That is what we 25 are doing right now, converting a java app 24 into stored procedures for maintainability 23 as the java code had become convoluted.

I 22 found this article on: The Business Logic Wars The author also 21 made the million rows in a table argument, which 20 I found interesting. He also added business 19 logic in javascript, which is client side 18 and outside of the business logic tier. I 17 hadn't thought about this before even though 16 I've used javascript for validation for 15 years, to along with server side validation.

My 14 opinion is that you want the business logic 13 in the application/middle tier as a rule 12 of thumb, but don't discount cases where 11 it makes sense to put it into the database.

One 10 last point, there is another group where 9 I'm working presently that is doing massive 8 database work for research and the amount 7 of data they are dealing with is immense. Still, for 6 them they don't have any business logic 5 in the database itself, but keep it in the 4 application/middle tier. For their design, the 3 application/middle tier was the correct 2 place for it, so I wouldn't use the size 1 of tables as the only design consideration.

Business logic is usually embodied by objects, and 9 the various language constructs of encapsulation, inheritance, and 8 and polymorphism. For example, if a banking 7 application is passing around money, there 6 may be a Money type that defines the business 5 elements of what "money" is. This, opposed 4 to using a primitive decimal to represent 3 money. For this reason, well-designed OOP 2 is where the "business logic" lives—not 1 strictly in any layer.