Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-11-01 : 08:33:19
|
| Sean P. O. MacCath-Moran writes "Hey gang! =o)I'm trying to generate some ideas here and was wondering if you folks would like to participate in the brainstorming. At my place of work, we have a product with a VB front end and a SQL back end. For the most part, the business rules are applied in the Add / Update stored procedures in SQL. I am now doing further development on the data import and validation processes.What I want to do is centralize all of our business rules. My current thinking is that I could build a stored procedure that knows how to execute rules (i.e. check for null, check for certain length, etc.) and then store all of the rules in a table which indicates what object (field, table, etc.) gets what business rule applied. Any ideas? Has anyone else had to tackle this sort of issue before? If so, would you mind sharing your approach?Many thanks,Sean P. O. MacCath-Moran" |
|
|
solart
Posting Yak Master
148 Posts |
Posted - 2002-11-01 : 12:14:32
|
| Here is a snippet.Overtime, the consensus of what I have seen in the forums seems to be that stored procedures should be created for doing table maintenance. The business rules are contained within the stored procedures.solart |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-11-01 : 13:30:53
|
| I concur. Put the rules in Stored Procs. No matter what app talks to your DB, as long as all the apps call the stored procs, they will all follow the same rules.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-11-01 : 15:43:58
|
| Sorry guys, you didn't answer Sean's question, basically only echoed his opinion that business rules should go in stored procs.Sean, if I get what you mean, you want to create a table called e.g. Rules, with a field e.g. RuleName of Varchar(x) and put into it the following:Table1.Field1 NOT NULLTable1.Field2 = 25etc. Looks like you'll have to deal with alot of dynamic SQL if you will try to check inserted/updated data against this kind of thing. Also, maybe someone who has a real understanding of performance (which is not me!!) might be able to tell you whether doing this will or won't degrade your performance.But otherwise, why not apply all rules directly to the table fields? If you want to store them in a user table, you are duplicating the functionality of SQL, which stores it's objects in the SysObjects table with a ParentId (what table or field gets this rule applied). Seems a stored procedure would be duplicating all this built in functionality. If your rules are applied to the base tables, what you can do in the SP is return errors if data is bad.Sarah Berger MCSD |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-06 : 15:16:00
|
| I agree with everyone here on this one. There are ways to enforce rules in the table structure itself, to keep data valid and properly formatted and such. Any other methods are procedural, and are best done with actual code. Storing code in a table and executing it dynamically would lead to lower performance, as Sarah already mentioned, and in any case it's trying to fit a square peg in a round hole. Data is data, code is code; they talk to each other but they're not the same thing, and trying to make one do the other's job is more trouble than it's worth. |
|
|
|
Tim
Starting Member
392 Posts |
Posted - 2002-11-06 : 21:47:02
|
| CREATE TRIGGERRAISE ERROR----Nancy Davolio: Best looking chick at Northwind 1992-2000 |
|
|
|
emanaton
Starting Member
8 Posts |
Posted - 2002-11-12 : 08:58:45
|
| Greets all! =o)Ok - first: Sorry for the duplicate posting of my inquiry in the forum! *blush* I hadn’t seen that it had been posted for discussion. Thank you for the redirect, Rob. =o)Second - Thank you all so much for your responses to date! I’m very sorry I didn’t get out here earlier to reply.The application in question currently has all of its business rules being executed by add / update stored procedures that are called by the respective modules in the VB front end. I am now fleshing out our data import (DI for short) mechanism, which must also execute the same business rules. Against my advice, the management team here has directed that DI will not directly call the existing add / update procedures as this would cause a dependency by the import routines on the integrity of procedures that other programmers have written, possibly causing errors that DI cannot handle. As stated in my initial post, I am now attempting to engineer a method of implementing the necessary business rules such that we (the other programmers who write business rules and DI) are not duplicating efforts but maintaining identical business rules in two locations.I have shied away from the use of triggers because it does not appear that I can build the more complicated business rules into them - or at least not easily. Also, triggers tend to be ‘out of sight - out of mind’. For this reason, and because we have a multi-programmer environment / situation, some sort of business rule implementation within stored procedures seems to be more preferable.All of this is why I was looking at placing the rules in a table and executing them dynamically. It seemed a slower but more manageable solution given my constraints. My preference would be to use a more elegant / less costly method, but I have not yet conceived of another approach which meets the requirements of being a ‘visible’ implementation that is both centralized and easily maintained by a group. So - given these further constraints, does anyone have a magic bullet for me? =o)Sean P. O. MacCath-Moran |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-14 : 11:14:12
|
quote:
Against my advice, the management team here has directed that DI will not directly call the existing add / update procedures as this would cause a dependency by the import routines on the integrity of procedures that other programmers have written, possibly causing errors that DI cannot handle...All of this is why I was looking at placing the rules in a table and executing them dynamically. It seemed a slower but more manageable solution given my constraints.
I might be missing something here, but...If an existing procedure cannot be used for a process that uses the same rules, how would storing the code for that procedure and executing it dynamically work any differently? It's the same code and will execute the same whether it's done through the DI process or not. Otherwise it would HAVE to be two or more separate procedures. Having said that, it makes little sense (to me anyway) to NOT use the procedure...I know you know that already, but you might want to go back to your idiots managers and point that out to them.I think what you really should do is re-evaluate how the code gets developed and implemented, not how it is executed (the part I highlighted in red) Business rules SHOULD be centralized, and part of that is keeping the various developers on the same page. If any of them can make up new code without anyone else being aware of it, you'll be spitting into the wind and will almost certainly have multiple versions of the same code...no centralization at all. If the rules for two processes are the same, they should be developed by the same person or people, or at least in conference with each other, and consolidated into ONE process. |
|
|
|
|
|
|
|
|
How best to centralize business rules? 