Business Logic & Stored Procedures

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL IF statement/function

Post by VladSun »

josh wrote:The paragraph immediately following the content you just pasted reads:
'The above scenario is the goal; however some duplication, especially for validation purposes should exist in the client as well. Such rules should be reinforced by the business layer. Furthermore in some systems for reasons of extreme performance benefits such as batch updates may cause an overriding exception and should be placed in the database. So a more realistic approach appears as follows. Note that 100% still exists in the business layer and that the minimal pieces that exist in other layers are actually duplications and exist solely for the purpose of performance or disabling user interface fields according to selections, etc.'
That's much better. :)
josh wrote:And actually I personally handle cascading deletes / etc.. in the business tier personally. That is actually preferable. Constraint validations if implemented should be in addition to the business logic in the business tier, IMO. Once a single piece of business logic is involved in an operation it should be moved into the business tier entirely in 99.99999% of situations.
Constraint validations implementation is a must - that ensures the integrity of your DB.
Frankly, I can't see how these two are so different: having a PHP code that catches constraint exceptions in multiple queries, or having a PHP code that catches exceptions fired by a call to a stored procedure.

Now let's talk about something different - DB transactions. How do a transaction relates to the business layer?
There are 10 types of people in this world, those who understand binary and those who don't
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: MySQL IF statement/function

Post by josh »

There's business transactions and system transactions, system transactions should be implemented w/ a transactional database but abstracted in the business layer, not implemented as stored procedures. Business transactions is like a user "session" that spans multiple page requests. Those should definitely have nothing to do with the database aside from maybe a row in a table that keeps track of the transaction. Thing is your tests should pass even if you delete those foreign key restraints. If your application depends on them you've leaked too much business logic and you're going down a slippery slope.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL IF statement/function

Post by VladSun »

VladSun wrote:Constraint validations implementation is a must - that ensures the integrity of your DB.
Frankly, I can't see how these two are so different: having a PHP code that catches constraint exceptions in multiple queries, or having a PHP code that catches exceptions fired by a call to a stored procedure.

Now let's talk about something different - DB transactions. How do a transaction relates to the business layer?
I meant exactly how DB transactions relate to the business layer. I didn't mentioned stored procedures...

Also I don't think " If your application depends on them you've leaked too much business logic and you're going down a slippery slope." argument wins over the "Constraint validations implementation is a must - that ensures the integrity of your DB."
There are 10 types of people in this world, those who understand binary and those who don't
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Business Logic & Stored Procedures

Post by josh »

ok keep writing code that contradicts the database schema then, you win.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Business Logic & Stored Procedures

Post by VladSun »

It's not about who's winning and who's not.

You haven't answered many of my questions. Let me summarize a few of them:

Triggers, foreign key constraints, cascades, etc. - what do you think of them in the context of business logic?
When they should be used? Are they redundant?

Stored procedures, functions - what do you think of them in the context of business logic?
When they should be used? Are they redundant?

DB transactions - what do you think of them in the context of business logic?
When it should be used?

DB table inheritance - what do you think of them in the context of business logic?
When it should be used?

DB generated exceptions - what do you think of them in the context of business logic?
When it should be used?

I really don't think the DB features quoted above can be forbidden only because someone says: "the database should have no functional knowledge of what exactly constitutes a customer in the business layer."
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Business Logic & Stored Procedures

Post by VladSun »

I'll be happy if there are other people here discussing this topic. It will be a way more objective for both of us.
There are 10 types of people in this world, those who understand binary and those who don't
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Business Logic & Stored Procedures

Post by josh »

VladSun wrote:Triggers, foreign key constraints, cascades, etc. - what do you think of them in the context of business logic?
When they should be used? Are they redundant?
I don't know anything about triggers, sorry. I dont think foreign key constraints are an issue as I keep saying since it defines shape of data, as per Dr. Codd's specifications.
VladSun wrote:Stored procedures, functions - what do you think of them in the context of business logic?
When they should be used? Are they redundant?
By definition these features embed business logic into the DBMS. They should not be used except in the exceptional situations that have been outlined in the article previously discussed. Not sure what you mean by redundant
VladSun wrote:DB transactions - what do you think of them in the context of business logic?
When it should be used?
I don't see how transactions could be construed as a misplacing of business logic. Unless you initiate the transaction inside of a stored procedure, which would be bad practice.
VladSun wrote:DB table inheritance - what do you think of them in the context of business logic?
When it should be used?
Again is shape and not behavior. This is what RDMS was designed for. ( granted OOP is a better inheritence paradigm ) but object databases is another set of problems we could debate.
VladSun wrote:DB generated exceptions - what do you think of them in the context of business logic?
When it should be used?
Depends if its an "application boundary". Database unavailable isn't business logic. If you're relying on the database to tell you things about your data consistency you've screwed up. Constraints are supposed to be the kind of "added security" you pointed out about using stored procedures to fight SQL injection. I think that's a valid argument but you'd be crazy to do it for soley preventing injection, there's better ways.
VladSun wrote:I really don't think the DB features quoted above can be forbidden only because someone says: "the database should have no functional knowledge of what exactly constitutes a customer in the business layer."
Noone said forbid them, they're still there for a purpose but like every tool has a potential for naive abuse.

PS: I'm happy to keep discussing this but when I answer the same questions multiple times it gets on my nerves.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Business Logic & Stored Procedures

Post by VladSun »

Triggers:

Code: Select all

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;
Foreign keys and cascades:

Code: Select all

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                      price DECIMAL,
                      PRIMARY KEY(category, id)) ENGINE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
                       PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                            product_category INT NOT NULL,
                            product_id INT NOT NULL,
                            customer_id INT NOT NULL,
                            PRIMARY KEY(no),
                            INDEX (product_category, product_id),
                            FOREIGN KEY (product_category, product_id)
                              REFERENCES product(category, id)
                              ON UPDATE CASCADE ON DELETE RESTRICT,
                            INDEX (customer_id),
                            FOREIGN KEY (customer_id)
                              REFERENCES customer(id)) ENGINE=INNODB;
It's all about logic which guarantees the integrity of the DB data. It doesn't matter whether your application side business logic will succeeded doing it.
What about multiple types of client-side applications (desktop, internet, services, etc.) - the application code which guarantees the integrity of the data usually is not going to be reused (e.g. C#/PHP/C used for the mentioned above).

By using "redundant" I meant "not to be used ever"
There are 10 types of people in this world, those who understand binary and those who don't
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Business Logic & Stored Procedures

Post by josh »

VladSun wrote:Triggers:

Code: Select all

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;
Smells like business logic.
VladSun wrote:It's all about logic which guarantees the integrity of the DB data.
The only reason the constraints should go off is if there is a communication error with the database. 100% of the business logic should be in the middle tier even if its duplicating a foreign key constraint. Your application should not rely on constraints.
VladSun wrote: What about multiple types of client-side applications (desktop, internet, services, etc.) - the application code which guarantees the integrity of the data usually is not going to be reused (e.g. C#/PHP/C used for the mentioned above).
Have no idea what your're saying here. If you mean client server, then yes, all business logic should exist in the middle tier, some duplication of business logic should occur on the client tier as well.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Business Logic & Stored Procedures

Post by VladSun »

josh wrote:
VladSun wrote:Triggers:

Code: Select all

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;
Smells like business logic.
VladSun wrote:It's all about logic which guarantees the integrity of the DB data.
The only reason the constraints should go off is if there is a communication error with the database. 100% of the business logic should be in the middle tier even if its duplicating a foreign key constraint. Your application should not rely on constraints.
It is business logic - that's my question: why there are some clever people that build DB engines and they put some nice features which should not be used ever :) So, why there are triggers, cascades, foreign constraints etc. when they should not be used?

I asked for examples just because I couldn't find (from your words and articles you pointed to) *any* suitable ones for *any* of these features.
But these features still exist, they are under hard development and they have to be used somehow. But ... how, where, when?

And all of these features implement some business logic ...
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Business Logic & Stored Procedures

Post by Eran »

If I may interject:
It is business logic - that's my question: why there are some clever people that build DB engines and they put some nice features which should not be used ever :) So, why there are triggers, cascades, foreign constraints etc. when they should not be used?
Personally I'm more with Josh on this one - but with reservation. I would support that keeping business logic out of the database is better for agile projects (which include most web projects), since it is more rigid and harder to change than application code. For more enterprise systems, keeping constraints in the database is the way to go, but for projects with highly fluid requirements it is too much of hassle to manage constraints and maintenance of stored procedures / triggers. Those features are mostly for the enterprise market, especially for companies that can afford a full-time DBA to manage this part of the application.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Business Logic & Stored Procedures

Post by josh »

Constraints aren't really business logic, as long as your application doesn't depend on them. Triggers / stored procedures are. Whether or not you choose to use constraints doesn't affect your architecture, as long as your application business logic is not relying on constraints. For instance if an A had a foreign key into B ( As have multiple Bs ) and in your code your application was using a constraint failure as a trigger to iterate the Bs and cascade the delete instruction, that would be bad. If A's class used a finder to query B's table directly and only used the constraint failure as a way to detect the delete instruction A failed, before it deleted all the Bs, that would be good practice.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Business Logic & Stored Procedures

Post by VladSun »

Triggers, cascades, foreign keys, unique constraints, etc. - all of this mirrors that part of the business logic which is responsible for keeping the integrity of the data. But it's done in a very natural, native way.
When you define a foreign key that references a field it's absolutely clear what you are doing. And it's absolutely clear why you are doing it. There are no IFs, no bugs (ok, let's say it is <0.000001% possibility), it is always checked. DB integrity should not rely on any application rules - simply it is the DB domain, not the application domain.

In a DB design one should have a knowledge what subjects are to be modeled, what are the relationships between them, what's the meaning of "data integrity" for this set of objects, etc. I really can't imagine how one will be able to create a DB design if he thinks it's only about storage ...
If it's so - create a table with BLOB fields and put serialized objects in it!
Or even use file system...

I can't imagine application that will put data into the DB "even if its duplicating a foreign key constraint" because that data doesn't make any sense any more.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Business Logic & Stored Procedures

Post by VladSun »

josh wrote:Constraints aren't really business logic, as long as your application doesn't depend on them. Triggers / stored procedures are. Whether or not you choose to use constraints doesn't affect your architecture, as long as your application business logic is not relying on constraints. For instance if an A had a foreign key into B ( As have multiple Bs ) and in your code your application was using a constraint failure as a trigger to iterate the Bs and cascade the delete instruction, that would be bad. If A's class used a finder to query B's table directly and only used the constraint failure as a way to detect the delete instruction A failed, before it deleted all the Bs, that would be good practice.
Imagine this:
- you have an application which puts some data into the DB;
- you decide that you need a second (absolutely separate) application that will work with the same DB;
- the second application needs to change some small parts of the DB which will affect onto the first application;

If the first application works with stored procedures you change them.
If the first application doesn't work with stored procedures you change the code.

Now imagine, that instead of having only one "first application" you have 10.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Business Logic & Stored Procedures

Post by Eran »

Imagine this:
- you have an application which puts some data into the DB;
- you decide that you need a second (absolutely separate) application that will work with the same DB;
- the second application needs to change some small parts of the DB which will affect onto the first application;
Simple, create an API. All database access should run through the same database abstraction code, which should be aware of validation and relationships.
Separate applications should interface with the primary application API instead of directly with the database. It's also much more secure if they're on separate servers (I wouldn't like to open my database to outside access).
Post Reply