 | |
Re: "Business Logic / Rules should never be in the database or storedprocedures"
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: "Business Logic / Rules should never be in the database or storedprocedures" -
12-13-2009
, 02:24 AM
|
* In my experience, those who argue for not having any business rules/logic in the database tend to be developers who are essentially lazy and refuse to learn anything about the database or its facilities. They just want to use it as a bit bucket and do absolutely everything in the application layer. Too often, this even includes basic data manipulation that could have been done more efficiently and resulted in clearer and more easily maintained code using SQL. This situation appears to have gotten worse with the growth in popularity of Java - essentially, code monkeys who just want to plug in API calls and who have no interest in learning all the tools available to them. they have mastered basic select, update and insert and thats as far as they want to go. When you are coming from this perspective, stored procedures are really just a way to make SQL 'easier and you get the CRUD way of thinking. |
And things like Hibernate tend to make this situation even worse as people ge the impression they don't need to think about the database anymore "because Hibernate takes care of that"
#2
| |||
| |||
|
|
* In my experience, those who argue for not having any business rules/logic in the database tend to be developers who are essentially lazy and refuse to learn anything about the database or its facilities. They just want to use it as a bit bucket and do absolutely everything in the application layer. Too often, this even includes basic data manipulation that could have been done more efficiently and resulted in clearer and more easily maintained code using SQL. This situation appears to have gotten worse with the growth in popularity of Java - essentially, code monkeys who just want to plug in API calls and who have no interest in learning all the tools available to them. they have mastered basic select, update and insert and thats as far as they want to go. When you are coming from this perspective, stored procedures are really just a way to make SQL 'easier and you get the CRUD way of thinking. Completely agree And things like Hibernate tend to make this situation even worse as people ge the impression they don't need to think about the database anymore "because Hibernate takes care of that" |
There is an underlying dilemma which I haven't seen any satisfying
solution to yet: you want enforcement of business rules in the database
in order to prevent any intentional or unintentional screw up of
application data. OTOH you want those rules in application code as
well, because there is where all the business logic resides.
From my experience what often happens is this: some basic business
constraints are enforced in the database (uniqueness, NOT NULL,
referential integrity, even some CHECK constraints) while the more
complex rules live in application code only.
From a redundancy point of view in an ideal world we had a single
source for business logic and extract application code as well as schema
based integrity checks from that. That probably will never work out of
the box because it omits aspects of physical deployment of data as well
as performance of checks. Also, checks might be done redundantly. And
we even haven't discussed schema migration yet...
Another option would be to place all the business logic in the database
and treat application logic as glue between UI and database only. With
Oracle we have a full features programming language that is tightly
integrated with SQL and would make coding application logic at least
feasible. This does not seem to be done frequently. Does anybody have
any experience with that?
Kind regards
robert
--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
#3
| |||
| |||
|
|
There is an underlying dilemma which I haven't seen any satisfying solution to yet: you want enforcement of business rules in the database in order to prevent any intentional or unintentional screw up of application data. OTOH you want those rules in application code as well, because there is where all the business logic resides. From my experience what often happens is this: some basic business constraints are enforced in the database (uniqueness, NOT NULL, referential integrity, even some CHECK constraints) while the more complex rules live in application code only. Another option would be to place all the business logic in the database and treat application logic as glue between UI and database only. With Oracle we have a full features programming language that is tightly integrated with SQL and would make coding application logic at least feasible. This does not seem to be done frequently. Does anybody have any experience with that? |
they happen to be stored there. If you have a stored procedure that is
called from somewhere (as opposed to a trigger), in my view this is just
application code like, say, a C program, only it's less integrated in the
overall development and tools landscape.
Joachim
#4
| ||||||
| ||||||
|
|
What is my problem ? Well assuming I am not misinterpeting things, my Enterprise Architect area and some non-Oracle knowledge architects feel that the business logic / rules should only be in the application tier. |
In the application layer it is closer to the user, so when you want to be user friendly ...
Quote:
|
Only Create, Read, Update, and Delete operations belong in stored procedures. |
will probably have functions to retrieve a column from a record, maybe a function where you
provide an id and the name of the column, which will be translated using dynamic sql. I've seen
programs where three functions were called to retrieve 3 columns from a record. Underneath the
complete record was fetched 3 times.
Quote:
|
Reason being that the database cannot process the business rules efficiently nor can they effectively be managed. |
My contention is that they care coming at it from a typical application perspective, not an
ERP perspective.
Quote:
|
I can understand workflow logic being externalized (aka Oracle Workflow), but the restricting stored procedures to CRUD operations only... would seem to belittle the power of the Oracle database and what it has to offer. |
awesome instrument, when looked after properly.
Quote:
|
Mind you, these other individuals have primary backgrounds and experience with SQL Server. This may be where some of there performance short sightedness may come from, you think ? |
Quote:
|
Anyways, am I off my rocker about sayings its a blend, but that there definitely exist many opportunities for the business logic / rules to exist in the database... and should. BChase bsc7080mqcXX (AT) myoracleportal (DOT) com (remove XX to contact) |
#5
| |||
| |||
|
|
+1 There is an underlying dilemma which I haven't seen any satisfying solution to yet: you want enforcement of business rules in the database in order to prevent any intentional or unintentional screw up of application data. OTOH you want those rules in application code as well, because there is where all the business logic resides. From my experience what often happens is this: some basic business constraints are enforced in the database (uniqueness, NOT NULL, referential integrity, even some CHECK constraints) while the more complex rules live in application code only. From a redundancy point of view in an ideal world we had a single source for business logic and extract application code as well as schema based integrity checks from that. That probably will never work out of the box because it omits aspects of physical deployment of data as well as performance of checks. Also, checks might be done redundantly. And we even haven't discussed schema migration yet... Another option would be to place all the business logic in the database and treat application logic as glue between UI and database only. With Oracle we have a full features programming language that is tightly integrated with SQL and would make coding application logic at least feasible. This does not seem to be done frequently. Does anybody have any experience with that? Yep. In fact, I just finished a project where I pretty much insisted |
access is via pl/sql packages. this is enforced by granting execute
privs to a separate schema, which is the schema the web UI is able to
connect to. the web UI essentially retrieves ref cursors to display data
via calls to plsql procedures/functions and sends back data via plsql
procedures. All the web UI does is handle the display of data and
provides forms for entering/updating data.
Originally, I was going to use Oracle for the front-end UI as well, but
that was taken out of my hands and given to our web developers, who
implemented the interface using Java with a combination of JSP and
Spring.
The application has been in production for a little over three months
now. Nearly all our problems have been due to the web interface. We ran
into a bug in Spring where it doesn't close cursors properly and a few
other issues due to the fact they adopted Spring part way through the
development. There were also some performance issues, all of which were
due to the web interface and not Oracle. The problem was the web team
used a form of reflection supported by Spring. Apparently (I'm no spring
or Java expert), you can use spring in such a way that it builds
procedure/function calls dynamically through a plsql procedure that
queries the dictionary tables to find out what the arguments for a
procdure are. Once I found this out, I instructed the web team to remove
this additional layer of waste and performance improved significantly.
Again, the problem was essentially due to the web developers not wanting
to know anything about the database or even the provided procedures -
they just wanted it to all work like magick!
Of course, we have had some bugs in the back-end. Nearly all have been
very simple and most essentially 'cosmetic' rather than serious.
As a result of the issues, I've now been given a free hand to fix things
and more importantly, choose the web developer to work on fixing the
interface. I found one who was more interested in actually
understanding how Oracle works, things to do and things to avoid when
working with Oracle and agrees with the basic principal of just letting
the front-end handle the interface etc. In the last 3 weeks, we have
made some really significant improvements and users are finally
beginning to get real value from the system.
The best part is i can now go back tofocusing on the second stage. This
is the most exciting part as this is where we plan to add some really
cool new functionality. I'm also pleased I seem to now have a good web
developer to work on the UI. I actually find UI design and
implementation quite boring, so I'm pleased to be owrking back on the
guts of the system, which for me has the more interesting and chalenging
problems.
One thing I'm hoping to be able to do soon is spend some time looking at
11g. Originally, I had thought of using Oracle's rule manager to
implement much of the business rules. However, in initial trials with
10g, I found rules manager just wasn't quite up to the task. While it
appeared to provide much of what I was looking for, I found it somewhat
unreliable. There were times when changes just didn't seem to take
effect and you frequently had to get the DBA to clean up internal tables
etc to get things back into a consistent state. My gut feeling was it
was just another example of a new Oracle feature that just wasn't quite
ready and you have to wait until the next release. I'm hoping the 11g is
a lot better. In the end, I essentially rolled my own solution, ripping
many of the ideas off from rule manager. So far, its worked really well.
Even since going live we have had to update/change some of the business
rules and this has proven to be both straight-forward and reliable.
However, I'd still like to try to use rule manager as I'd rather let
oracle take care of the maintenance of that code instead of me!
Tim
--
tcross (at) rapttech dot com dot au
#6
| |||
| |||
|
|
BChase wrote: ... What is my problem ? Well assuming I am not misinterpeting things, my Enterprise Architect area and some non-Oracle knowledge architects feel that the business logic / rules should only be in the application tier. Business logic in the database has faster access to the data, so if a lot of data is needed ... In the application layer it is closer to the user, so when you want to be user friendly ... Only Create, Read, Update, and Delete operations belong in stored procedures. A number of expressions spring to mind that I'd better not put into writing. These architects will probably have functions to retrieve a column from a record, maybe a function where you provide an id and the name of the column, which will be translated using dynamic sql. I've seen programs where three functions were called to retrieve 3 columns from a record. Underneath the complete record was fetched 3 times. Reason being that the database cannot process the business rules efficiently nor can they effectively be managed. Is that so? My contention is that they care coming at it from a typical application perspective, not an ERP perspective. I can understand workflow logic being externalized (aka Oracle Workflow), but the restricting stored procedures to CRUD operations only... would seem to belittle the power of the Oracle database and what it has to offer. Since you are using Workslow, I suppose performance is no issue? The Oracle database is an awesome instrument, when looked after properly. Mind you, these other individuals have primary backgrounds and experience with SQL Server. This may be where some of there performance short sightedness may come from, you think ? Never underestimate an opponent, and it would be "their" shortsightedness. Anyways, am I off my rocker about sayings its a blend, but that there definitely exist many opportunities for the business logic / rules to exist in the database... and should. BChase bsc7080mqcXX (AT) myoracleportal (DOT) com (remove XX to contact) Quite right. Not that you are of your rocker, that is. |
something I've often seen in applications that have the business rules
in the applicaiton layer and none in the database (i.e. basic CRUD) is
much much larger data retrievals/transfers. These systems tend to do
little filtering of the data at the db level. instead, they retrieve
large chunks of data and then filter it at the app level and then curse
Oracle for being inefficient! It stuns me that people think something
like Java sorting and filtering out data will be more efficient than
doing it at the SQL level.
Tim
--
tcross (at) rapttech dot com dot au
#7
| |||
| |||
|
|
On 13.12.2009 09:24, Thomas Kellerer wrote: * In my experience, those who argue for not having any business rules/logic in the database tend to be developers who are essentially lazy and refuse to learn anything about the database or its facilities. They just want to use it as a bit bucket and do absolutely everything in the application layer. Too often, this even includes basic data manipulation that could have been done more efficiently and resulted in clearer and more easily maintained code using SQL. This situation appears to have gotten worse with the growth in popularity of Java - essentially, code monkeys who just want to plug in API calls and who have no interest in learning all the tools available to them. they have mastered basic select, update and insert and thats as far as they want to go. When you are coming from this perspective, stored procedures are really just a way to make SQL 'easier and you get the CRUD way of thinking. Completely agree And things like Hibernate tend to make this situation even worse as people ge the impression they don't need to think about the database anymore "because Hibernate takes care of that" +1 There is an underlying dilemma which I haven't seen any satisfying solution to yet: you want enforcement of business rules in the database in order to prevent any intentional or unintentional screw up of application data. OTOH you want those rules in application code as well, because there is where all the business logic resides. From my experience what often happens is this: some basic business constraints are enforced in the database (uniqueness, NOT NULL, referential integrity, even some CHECK constraints) while the more complex rules live in application code only. From a redundancy point of view in an ideal world we had a single source for business logic and extract application code as well as schema based integrity checks from that. That probably will never work out of the box because it omits aspects of physical deployment of data as well as performance of checks. Also, checks might be done redundantly. And we even haven't discussed schema migration yet... Another option would be to place all the business logic in the database and treat application logic as glue between UI and database only. With Oracle we have a full features programming language that is tightly integrated with SQL and would make coding application logic at least feasible. This does not seem to be done frequently. Does anybody have any experience with that? Kind regards robert |
and has been quite popular in the early/mid 2000's for Oracle Designer
projects. Database logic (triggers) called the same (pl/sql) code that
application logic (pl/sql) did. All table updates from the application
was done through api's which called table-api's, but when an update was
performed on the table directly, the triggers would call the table-api.
Application performance was ok most of the time, despite of all the
(coded) overhead caused; biggest problem was debugging, specially when
Change Event Rules were modeled using this framework.
Shakespeare
#8
| |||
| |||
|
|
As a result of the issues, I've now been given a free hand to fix things and more importantly, choose the web developer to work on fixing the interface. I found one who was more interested in actually understanding how Oracle works, things to do and things to avoid when working with Oracle and agrees with the basic principal of just letting the front-end handle the interface etc. In the last 3 weeks, we have made some really significant improvements and users are finally beginning to get real value from the system. .... Tim |
Gerard
#9
| |||
| |||
|
|
Some good points and reminds me of something else I forgot to mention. something I've often seen in applications that have the business rules in the applicaiton layer and none in the database (i.e. basic CRUD) is much much larger data retrievals/transfers. These systems tend to do little filtering of the data at the db level. instead, they retrieve large chunks of data and then filter it at the app level and then curse Oracle for being inefficient! It stuns me that people think something like Java sorting and filtering out data will be more efficient than doing it at the SQL level. Tim |
#10
| |||
| |||
|
|
Robert Klemme <shortcutter (AT) googlemail (DOT) com> writes: +1 There is an underlying dilemma which I haven't seen any satisfying solution to yet: you want enforcement of business rules in the database in order to prevent any intentional or unintentional screw up of application data. OTOH you want those rules in application code as well, because there is where all the business logic resides. From my experience what often happens is this: some basic business constraints are enforced in the database (uniqueness, NOT NULL, referential integrity, even some CHECK constraints) while the more complex rules live in application code only. From a redundancy point of view in an ideal world we had a single source for business logic and extract application code as well as schema based integrity checks from that. That probably will never work out of the box because it omits aspects of physical deployment of data as well as performance of checks. Also, checks might be done redundantly. And we even haven't discussed schema migration yet... Another option would be to place all the business logic in the database and treat application logic as glue between UI and database only. With Oracle we have a full features programming language that is tightly integrated with SQL and would make coding application logic at least feasible. This does not seem to be done frequently. Does anybody have any experience with that? Yep. In fact, I just finished a project where I pretty much insisted that was the design. All the business logic is in the database. The only access is via pl/sql packages. this is enforced by granting execute privs to a separate schema, which is the schema the web UI is able to connect to. the web UI essentially retrieves ref cursors to display data via calls to plsql procedures/functions and sends back data via plsql procedures. All the web UI does is handle the display of data and provides forms for entering/updating data. Originally, I was going to use Oracle for the front-end UI as well, but that was taken out of my hands and given to our web developers, who implemented the interface using Java with a combination of JSP and Spring. The application has been in production for a little over three months now. Nearly all our problems have been due to the web interface. We ran into a bug in Spring where it doesn't close cursors properly and a few other issues due to the fact they adopted Spring part way through the development. There were also some performance issues, all of which were due to the web interface and not Oracle. The problem was the web team used a form of reflection supported by Spring. Apparently (I'm no spring or Java expert), you can use spring in such a way that it builds procedure/function calls dynamically through a plsql procedure that queries the dictionary tables to find out what the arguments for a procdure are. Once I found this out, I instructed the web team to remove this additional layer of waste and performance improved significantly. Again, the problem was essentially due to the web developers not wanting to know anything about the database or even the provided procedures - they just wanted it to all work like magick! Of course, we have had some bugs in the back-end. Nearly all have been very simple and most essentially 'cosmetic' rather than serious. As a result of the issues, I've now been given a free hand to fix things and more importantly, choose the web developer to work on fixing the interface. I found one who was more interested in actually understanding how Oracle works, things to do and things to avoid when working with Oracle and agrees with the basic principal of just letting the front-end handle the interface etc. In the last 3 weeks, we have made some really significant improvements and users are finally beginning to get real value from the system. The best part is i can now go back tofocusing on the second stage. This is the most exciting part as this is where we plan to add some really cool new functionality. I'm also pleased I seem to now have a good web developer to work on the UI. I actually find UI design and implementation quite boring, so I'm pleased to be owrking back on the guts of the system, which for me has the more interesting and chalenging problems. One thing I'm hoping to be able to do soon is spend some time looking at 11g. Originally, I had thought of using Oracle's rule manager to implement much of the business rules. However, in initial trials with 10g, I found rules manager just wasn't quite up to the task. While it appeared to provide much of what I was looking for, I found it somewhat unreliable. There were times when changes just didn't seem to take effect and you frequently had to get the DBA to clean up internal tables etc to get things back into a consistent state. My gut feeling was it was just another example of a new Oracle feature that just wasn't quite ready and you have to wait until the next release. I'm hoping the 11g is a lot better. In the end, I essentially rolled my own solution, ripping many of the ideas off from rule manager. So far, its worked really well. Even since going live we have had to update/change some of the business rules and this has proven to be both straight-forward and reliable. However, I'd still like to try to use rule manager as I'd rather let oracle take care of the maintenance of that code instead of me! |
similar ideas for one application in our company but so far I'm the only
one proposing this. Part of the reason might be that Oracle knowledge
is really sparse and / or people don't believe that good app logic can
be implemented in a non "application" programming language. Funny
though that what we have now is a procedural API implemented in an
object oriented language...
Kind regards
robert
--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.