| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-04-09 : 08:20:56
|
| 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
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-04-09 : 08:36:47
|
| 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 AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-04-09 : 09:38:44
|
| 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.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-04-09 : 10:36:44
|
| 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 |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2007-04-09 : 16:55:52
|
| >>but Views can still be by-passedI 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.. SNIPThat will occur when you SELECT from the view. Practically, you would SELECT straight from the base table (reducing IO).DavidMProduction is just another testing cycle |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-04-09 : 18:39:34
|
| 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 |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2007-04-09 : 18:56:47
|
| >>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.DavidMProduction is just another testing cycle |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-04-09 : 18:57:40
|
| To me, that adds much more complexity and margin for error than using a trigger.e4 d5 xd5 Nf6 |
|
|
|
rajgo
Starting Member
2 Posts |
Posted - 2007-04-11 : 13:40:16
|
| 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/)Rajgohttp://qrdn.brmsblog.com |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-04-11 : 15:16:18
|
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 |
|
|
|
rajgo
Starting Member
2 Posts |
Posted - 2007-04-12 : 04:38:37
|
| 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?Rajgohttp://qrdn.brmsblog.com |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-04-12 : 08:08:16
|
| 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 |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2007-04-20 : 03:36:08
|
| 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 |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2007-04-20 : 06:09:19
|
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.DavidMProduction is just another testing cycle |
|
|
|
PSamsig
Constraint Violating Yak Guru
384 Posts |
Posted - 2007-04-21 : 18:40:07
|
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. |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2007-04-22 : 03:51:18
|
| 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 |
|
|
|
|
Article: Using Views to Enforce Business Rules