I've worked in some projects where most of the business logic was implemented on the database (mostly through stored procedures). On the other side, I've heard from some fellow programmers that this is a bad practice (Databases are there to store data. Applications are there to do the rest).Which of these approaches is the generally better?The pros of implementing business logic in the DB I can think of are:Centralization of business logic;Independency of application type, programming language, OS, etc;Databases are less prone to technology migration or big refactorings (AFAIK);No rework on application technology migration (e.g.: .NET to Java, Perl to Python, etc).The cons:SQL is less productive and more complex for business logic programming, due to the lack of libraries and language constructs the most application-oriented languages offer;More difficult (if possible at all) code reuse through libraries;Less productive IDEs.Note: The databases I'm talking about are relational, popular databases like SQL Server, Oracle, MySql etc.Thanks!
How much business logic should the database implement?
database;application design;business logic;business rules
Business logic doesn't go into the databaseIf we're talking about multi-tier applications, it seems pretty clear that business logic, the kind of intelligence that runs a particular enterprise, belongs in the Business Logic Layer, not in the Data Access Layer.Databases do a few things really well:They store and retrieve dataThey establish and enforce relationships between different data entitiesThey provide the means to query the data for answersThey provide performance optimizations.They provide access controlNow, of course, you can codify all sorts of things in a database that pertain to your business concerns, things like tax rates, discounts, operation codes, categories and so forth.  But the business action that is taken on that data is not generally coded into the database, for all sorts of reasons already mentioned by others, although an action can be chosen in the database and executed elsewhere.And of course, there may be things that are performed in a database for performance and other reasons:Closing out an accounting periodNumber crunchingNightly batch processesFail-overNaturally, nothing is engraved in stone.  Stored Procedures are suitable for a wide array of tasks simply because they live on the database server and have certain strengths and advantages.  Stored Procedures EverywhereThere's a certain allure to coding all of your data storage, management and retrieval tasks in stored procedures, and simply consuming the resulting data services.  You certainly would benefit from the maximum possible performance and security optimizations that the database server could provide, and that's no small thing.  But what do you risk?Vendor lock-inThe need for developers with special skill setsSpartan programming tools, overallExtremely tight software couplingNo separation of concernsAnd of course, if you need a web service (which is probably where this is all heading, anyway), you're still going to have to build that.So what is typical practice?I would say that a typical, modern approach is to use an Object-Relational Mapper (such as Entity Framework) to create classes that model your tables.  You can then speak to your database through a repository that returns collections of objects, a situation that is very familiar to any competent software developer.  The ORM dynamically generates SQL corresponding to your data model and the information requested, which the database server then processes to return query results.How well does this work?  Very well, and much more rapidly than writing stored procedures and views.  This generally covers about 80% of your data access requirements, mostly CRUD.  What covers the other 20%?  You guessed it: stored procedures, which all of the major ORMs support directly. Can you write a code generator that does the same thing as an ORM, but with stored procedures?  Sure you can.  But ORMs are generally vendor-independent, well-understood by everyone, and better supported.