SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using Views to Enforce Business Rules
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 04/09/2007 :  08:20:56  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
A view is most commonly thought of as a SELECT statement. Most developers will simply create a view to "group" complex SELECT statements for reuse within another view or stored procedures. It makes typing easier! But the really power of views is their ability to implement business rules.

Article Link.

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 04/09/2007 :  08:36:47  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
But this trick is only useful when we are using updatable views through which we can insert/update data. I am not sure how many people use View for that purpose due to limitations posed.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/09/2007 :  09:38:44  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Great stuff, David. I think you and I are the only guys out there who use Views on a regular basis sometimes ....

Harsh -- you could handle that via permissions; don't give insert permissions to the underlying table, only to the View.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/09/2007 :  10:36:44  Show Profile  Reply with Quote
Nifty, but Views can still be by-passed so I still think this is better implemented with a trigger. Scope-wise, rules about data belong with the data. For instance, in your example if the customer's invoice limit changes (drops, for instance), then prior invoices will be excluded from the result set. I doubt that is the behavior that the application developers would expect.

e4 d5 xd5 Nf6
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 04/09/2007 :  16:55:52  Show Profile  Reply with Quote
>>but Views can still be by-passed

I don't buy that blindman. That's like saying a primary key can be bypassed.

>>then prior invoices will be excluded from the result set.. SNIP

That will occur when you SELECT from the view. Practically, you would SELECT straight from the base table (reducing IO).

DavidM

Production is just another testing cycle
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/09/2007 :  18:39:34  Show Profile  Reply with Quote
Yes, but primary keys certainly cannot be as easily by-passed as a view. Of course if you are intent on hosing up a database and you have sufficient permissions, nothing is going to stop you.

Are you saying that the application should select from one object (the table) and insert/update another object (the view)?

e4 d5 xd5 Nf6
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 04/09/2007 :  18:56:47  Show Profile  Reply with Quote
>>Are you saying that the application should select from one object (the table) and insert/update another object (the view)?

Yes I am. Practically, this is the most efficient way to work with an updateable view.

DavidM

Production is just another testing cycle
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/09/2007 :  18:57:40  Show Profile  Reply with Quote
To me, that adds much more complexity and margin for error than using a trigger.

e4 d5 xd5 Nf6
Go to Top of Page

rajgo
Starting Member

India
2 Posts

Posted - 04/11/2007 :  13:40:16  Show Profile  Visit rajgo's Homepage  Reply with Quote
Hi,

I think that Business Rules Must be close to the Data is not applicable at all times, and situations.

Externalizing Business Rules from the application is a good pattern, and needs to be encouraged. The issue as I understand with your approach is that maintenance would then require expert IT involvement.

Even if your Business Analysts are not willing to code, it is going to be tough to get them to work(if at all) with database stored procs that have rules embedded in them. Most probably your Business will become blind to these rules.

Another problem I believe is that with this model, it would require far too much careful programming to clearly separate business rules and application specific SQL.
That means that any change in just the rules will still lead to doubts, and hence a bigger testing cycle than required.

A third point is that we should stop treating business rules as software requirements(http://qrdn.brmsblog.com/2007/04/07/business-rules-software-requirements/)





Rajgo
http://qrdn.brmsblog.com
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/11/2007 :  15:16:18  Show Profile  Reply with Quote
quote:
Originally posted by rajgo

Externalizing Business Rules from the application is a good pattern, and needs to be encouraged.

Externalizing Business Rules is a lousy idea, and inevitably leads to static application designs, conflicting rules, and corrupted data. Argue with a developer about the reasons for not coding data rules in the database and eventually their reasoning always reduces to "I don't know how to code SQL".

e4 d5 xd5 Nf6
Go to Top of Page

rajgo
Starting Member

India
2 Posts

Posted - 04/12/2007 :  04:38:37  Show Profile  Visit rajgo's Homepage  Reply with Quote
Not sure why you say that.
It is a lousy idea if your business rules never change.

But, if your rules are changing often, then it makes sense to externalize them. Using a Rule Engine is one solution, but externalization can be done by other means too!

All your reasons (>> static application designs, conflicting rules, and corrupted data), I do not see how externalization causes all this.

A good programming advice normally is to separate/abstract out those parts that change often. I fail to understand why you would resist something like that?

Programmers are not experts in Business Rules. Then, why the resistance is capturing,implementing them in a form that will allow exterts (analysts) to be able to participate in the development and change process?



Rajgo
http://qrdn.brmsblog.com
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/12/2007 :  08:08:16  Show Profile  Reply with Quote
Stored procedures are the separation between the data and the interface.

It makes the MOST sense to implement your business rules in the database when they change frequently. If you implement your rules in sprocs and views, and limit access to the database to those sprocs and views, the every application that accesses the database follows the same rules and any change to those rules affects all the applications. When you externalize the rules that opens the door for applications and interfaces to bypass them or create their own versions of the rules.

Why does it makes sense to spread the logic of an application across several technologies and locations if those rules may change frequently? That makes absolutely no sense at all. I'll quote Mark Twain: "Put all your eggs in one basket. And then WATCH THAT BASKET!"


e4 d5 xd5 Nf6
Go to Top of Page

henrikop
Constraint Violating Yak Guru

Netherlands
280 Posts

Posted - 04/20/2007 :  03:36:08  Show Profile  Visit henrikop's Homepage  Reply with Quote
If you use the updateable view to enforce a business rule how do you implement that into an application?

Do you execute the SQL Commando (INSERT INTO dbo.UpdateAbleViews (...) VALUES (...))? from the interface and put a "try catch" around it? I think not because of the performance hit of an error. Or do you do a transaction around it? and role back if a sql error occurs?

I see the beauty of the business rule embedden in a view, but to embrace this I need some practical extra information how to implement it, else there's no reason to consider using it.

What I've learned is that you do validation before executing any insert so that you know that in any normal situation the insert always succeeds.

Arguing about external or internal ways to enforce businessrules is useless. It depends on the situation and the developer(s) making it. There's always pro's and cons regarding complexity, maintainability, performance, geoghrapy, etc.



Henri
~~~~
There's no place like 127.0.0.1
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 04/20/2007 :  06:09:19  Show Profile  Reply with Quote
quote:
Originally posted by henrikop

Do you execute the SQL Commando (INSERT INTO dbo.UpdateAbleViews (...) VALUES (...))? from the interface and put a "try catch" around it? I think not because of the performance hit of an error. Or do you do a transaction around it? and role back if a sql error occurs?

SNIP......

What I've learned is that you do validation before executing any insert so that you know that in any normal situation the insert always succeeds.



I deliberately bolded 2 phrases. You cannot have it both ways.

The view does not require transaction management. It either succeeds or fails.

quote:

There's always pro's and cons regarding complexity, maintainability, performance, geoghrapy, etc.



No arguments there.

DavidM

Production is just another testing cycle
Go to Top of Page

PSamsig
Constraint Violating Yak Guru

Denmark
384 Posts

Posted - 04/21/2007 :  18:40:07  Show Profile  Send PSamsig a Yahoo! Message  Reply with Quote
quote:
What I've learned is that you do validation before executing any insert so that you know that in any normal situation the insert always succeeds.


What I have learned is that there is nothing like a normal situation, only desired ones, that is why try/catch and transactions is a must.

I will agree, that in the proposed example, I would have tested for the the contrain condition beforehand, but only to give the user a proper error message, althoug THAT is inefficient, but it doesn't prevent to use the updateable view to ensure data intregrity anyway, AND of couse it should all be done in some combination of a try/catch/transaction ... all in the name of integrity.

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.
Go to Top of Page

henrikop
Constraint Violating Yak Guru

Netherlands
280 Posts

Posted - 04/22/2007 :  03:51:18  Show Profile  Visit henrikop's Homepage  Reply with Quote
Nice signature!! How true it is.. someone tought me...

Agree with try catch (and Transaction for that matter). With normal I meant database connection, running server, no time-outs etc.

Still the article gave me food for thought. I never considered it.

Henri
~~~~
There's no place like 127.0.0.1
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000