Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database in postgres with 147 tables; these tables contain data generated by functions that have most of the logic of the system, for example how to build an accounting entry.

These functions are called by an application in java that has almost no business logic in the code.

This database structure is used for different clients and some of these functions have small adaptations per customer with comment in the function body.

  1. This approach where most of the logic is in the database is correct?

  2. If you update a function in a database and we must pass it to each other how we can easily distinguish which function has changes To don’t delete or replace a change in client database by error?

share|improve this question
2  
You might be interested in this: ledgersmbdev.blogspot.de/2012/01/… and this: ledgersmbdev.blogspot.de/2012/02/… and for managing schema migrations take a look at Liquibase or Flyway –  a_horse_with_no_name Nov 21 '13 at 7:05

3 Answers 3

up vote 13 down vote accepted

Two queries - two replies:

a) Placing business logic to database has strong defenders and strong opponents. Lot of arguments for/against are volatile and valid only for some configurations and environment. Some databases has not good capabilities for stored procedural programming, some companies has not good personal resources for programming in relative different environments. Some projects doesn't require really effective data processing and more valuable is simply monolithic deployment. There are a some arguments for placing business logic to database (only processes that are not INTERACTIVE!). You have to divide application to interactive (presentation) layer and non interactive layer (what is usually a best way, what you can do):

  • natural decomposition of your application between data processing and data presentation layers
  • very well accessible API in heterogeneous environment - logic in database is accessible from shell scripts, all languages, ...
  • very efficient data processing due closeness data and processing unit (in PostgreSQL it is same process). It removes a network latency, conversions between protocols, layers, drivers - PL/pgSQL runs in same process as PostgreSQL core database engine and it use same data types.
  • you can create a API based on stored procedures, so any changes on database layer are transparent for you and for your application.

Generally I can say, a power of stored procedures is more significant in heterogenous environment and less in monolititic single application environment.

b) PostgreSQL has a CREATE OR REPLACE FUNCTION statement and data visibility based on snapshots - so update in production usually is not problem. You can update database dictionary (with functions) under transaction.

share|improve this answer
    
Pavel, does the following mean that we can always replace a function while it is being executed? "PostgreSQL has a CREATE OR REPLACE FUNCTION statement and data visibility based on snapshots - so update in production usually is not problem" –  AlexKuznetsov Nov 24 '13 at 21:13
    
yes, functions (a body of function) can be changed 100% safely (without any unavailability). Invalidation of cached function syntax trees works perfect. –  Pavel Stehule Nov 24 '13 at 21:42
1  
This should be the accepted answer. –  Erwin Brandstetter Nov 25 '13 at 20:13

As for your first question;

Business logic is most commonly best kept in a business logic space in your application layer and not in connection with the data itself. It is the multi tiered approach to limit complexity and follow various code "best practices".

The database is there to keep your data safe, and basically - that is what you should do with it.

One reason for this is that business logic often can become quite complex, it is volatile and will change more often than the data itself will change (Product data is product data, but how to treat the data, calculate and display, changes more often, for example).

Another reason is that business logics complexity often means that you'll perform operations, and those operations will often be much faster to perform in a code layer. Data base systems are heavily optimized for querying (relational models for set based queries etc) and not for doing complex calculations and manipulations.

Also the maintainability is often much higher in a code/application layer. For many teams it's easier to track changes in an application code layer as many seem to not set up source control for a database. Also, with a code layer you get better type handling, many more programming tools and techniques to go with problem solving.

So, the database is good at handling data, storing and querying (well, it's good if done "correctly") and application code layers are good at application work, so the general rule of thumb, is to keep the business logic in the business logic space of the application. Granted some database servers try to merge these two spheres of systems - but the rule of thumb is there for a reason.

Now, all these rules of thumbs aside, then there are situations where business logic do 'creep' into the database and occasionally are better kept in the database. For example with import/export/migration functions kept fully in the database to avoid pulling a lot of data into a code layer to push it down again. Then often you'll have some minor business rules in the database in connection with a query, such as an Active flag might be changed from a true/false value to a Yes/No value in an export query, because that's what "they" want to receive on the other end. But these are generally minor rules.

So as with all things you'll need to evaluate your own requirements for the system/project; analyze what the data is and how it is used; and then after that you can design your system and where to handle data modifications.

As for your second question, it sounds like you'll need some form of source control system, just like you would have with application code. I'm unsure which systems function for postgresql - but I bet there are some out there. Some do it by scripting the database functions, tables and similar into a system like SVN so changes can be tracked

share|improve this answer
5  
Isn't the schema design itself a part of business logic? Especially, the integrity constraints come to mind... Furthermore, what you join together in a query is also part of the business logic, while this is the best to keep as close to the DB as possible (which happens to be inside, in the form of views and/or stored procedures). Also, there are well-established solutions for versioning DB objects, you don't have to develop an own system for that. Let me add that there are certain things which should not be done in the DB. I wouldn't try resizing images, for example. –  dezso Nov 21 '13 at 17:23
    
Schema's are more for data integrity than business logic, and querying is for extraction of the data 'chucks' so you can do something meaningful with it. Sure there will be some semantic overlap, but for example saying an order needs orderlines has more to do with data storage, but the statemachine that handles when to ship the order has more to do with business logic. So while the overlap exists in semantics, the database still is there for you to store/retrieve data whereas the business logic is how you use the data. –  Allan S. Hansen Nov 21 '13 at 19:11
    
And along the same line of reasoning - one could start calling presentation layer for business logic because it display the result of the business logic and needs to be implemented as such. However - even with that semantic overlap - the spheres of "responsibility" are still pretty commonly understood in a multi-tiered application. Whether it's always obeyed (or should be obeyed) in a project is another matter entirely ;) –  Allan S. Hansen Nov 21 '13 at 19:14

Personally, I don't like the solutions which put business logic into the database. My experience is the business logic is not always stable, sooner or later it will change. So your database functions or stored procedures will have to be modified, even the table structure will change sometimes. It's not easy to trace and debug inside the database. That's why we will use multi tier design: database, data access tier, business logic tier, presentation tier.

share|improve this answer
    
Well, tables keep changing in most cases anyway. With a well defined (and versioned) API schema it is very easy to follow the changes. –  dezso Nov 21 '13 at 8:29

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.