Where Logic Lives
-
Agreed... I think I'll go have some now. We have our Boston-area "geek dinner" tonight
http://nerddinner.com/blogs/boston/archive/2005/08/17/6816.aspx
--
Adam Machanic
whoisactive -
Adam,
I think we are going to have to agree to disagree. Your example in no way represents business logic. That is simply a data rule. Again, I urge you to check your definition of business logic. As for Peter's trust issue, of course there are constraints on the table, there is referential integrity built into the design of the DB, but that isn't business logic. Think of it this way, business logic is decided by the business and is subject to change. Margin trading isn't decided by company ABC, it is decided by a set of rules that superceede that business. The logic that decides if trader A is qualified for a margin account is business logic and once that is decided then your data validation rule comes into play.
It would really help me if you could give me a real world example of something you have done that speaks to this issue.
Also, I am truely hurt that I wasn't invited to the beer discussion at PASS.
-
Hmmm, getting around the semantics of 'business logic', 'application logic', and 'data validation', I'd like to propose that any logic, the avoidance of which may produce rows in the database which do not reflect true propositions, belongs inside the DBMS and should be a function for the DBMS to enforce.
And then again, I might be wrong ...
David Webb -
Mike,
No one said that the logic to determine whether or not the trader was qualified for a margin account wouldn't also be represented in the database. As a matter of fact, it probably should be! Otherwise, can you guarantee that the application made the right choice?
The beer, alas, was supposed to be a quaff in the name of agreement, not debate
--
Adam Machanic
whoisactive -
I would like to add a bit more of clarity to what I said before:
The minute you declare a Column data type you are using BUSSINESS RULES
The minute you use CHECK Constraints or ANY kind of constraints you are applaying BUSSINESS RULES
The minute you specify LOGIC ORDER of operations in your stored procedures you are using BUSSINESS RULES
I am not asking to to program a Maximun of Pontryagin algorithm or Ruffini's root detection algorithm for 1000th root. Just what is best to establish in the database should be there and what is best to write in the middle tier should be there!
The SET based approach to apply certain calculations on the middle tear should be avoided if possible can you think of a 20 Million records going back and forth between severs!? If it is possible to apply those rules on a database I would! then again sometimes is not and I have nothing againts that!
IT is in my opinion not a clear cut!
IF you want maximum flexibility would you make all your columns varchar(8000) and that way you can handle every value and variation in length and data type?
We don't need to go to extremes and we should take care as much as we can of data quality and as close as possible to the data!!
Just my $0.02 or should I said my £0.02 (I am in London today
)Beer!!!
* Noel -
Hi all
and thank you for contributing with thoughts and ideas I will bring on down the road of designing.
I'm a lazy b#st#rd so I put all logic into my db. If I need to change the logic (hmmm....) I don't need to re-compile my front-end project - but, as you all know - I end up re-compiling the project anyways...
But as far as I know, shouldn't there be a middle-tier that holds the logic?
DB is for data - Front-end is for viewing, the logic goes in between, into a dll for instance.
(I don't know if this approach is applicable in a asp-app.)
Could anyone set me straight?
/Ola L M
-
David I agree that much of this discussion is based on semantics and your proposal "that any logic, the avoidance of which may produce rows in the database which do not reflect true propositions, belongs inside the DBMS and should be a function for the DBMS to enforce." is correct. I would add that any logic that does not directly address these requirements be placed elsewhere.
[edited to protect the good name of Mike C by using my full name}
Mike Du Bois
All this talk of beer and its too early. I think I will have an Irish coffee instead.
-
I wish I had gotten in on this discussion earlier.
I have written applications with logic on the database server, and logic in the middle tier. I try to put all of my logic in stored procedures for smaller and mid-size applications. If I know that I am not going to outgrow my SQL Server box , I put my logic on the server. For instance, if the application has less then 1000 users taking phone orders, a dual processor server with 4G of ram is sufficient and even has room to grow. So scaling out would never be necessary.
Why keep the logic on the server? Performance! There is no faster way to get data to a user than having SQL Server spit out data that is ready to use. If SQL server sends data to the application server that needs additional processing, it will take longer before the user sees the data. The application server can't process the data any faster than the database server can, and it often can't do it nearly as fast.
This only works well if you are not stressing your SQL Server box. If you are keeping your processors pegged at 100% and you can't scale up, then having the logic on the server is a performance hazzard. This is when you should share the work load and let the application servers handle as much work as possible, leaving the database server to handle data requests only.
Bill
-
"I try to put all of my logic in stored procedures for smaller and mid-size applications. "
I do the same. My philosophy is to use stored procedures to transform data into information, then make that information available to application programs. The dividing line is generic vs. specific: if a certain type of information needs to be made available to all applications, I put the logic in a stored procedure - if it is specific to one application, provide that application with the necessary base information and let the application code perform any additional data manipulation needed.
It helps that the applications involved are analysis intensive and don't use all that much data - this philosophy might work poorly when applied to a few million ATM transaction records in a terabyte database.
-
Mike
Excellent article. Just last week I had a tough time with my frontend dev team. I refused to add string formatting in stored procedures and the dev team was almost forcing me to do it in SP. Finally I took my stand and said NOOO. When the manager asked the develpers what is the problem in doing the formatting in the front end, there was a quite silence and they agreed to do it in the front end. Later i did a google and sent them an article which clearly explains why string manipulations should be avoided in stored procedures
I wish the front end developers understand that for the performance of the application it is a good idea not to load the SQL Server.
No one thinks of longterm impacts, they just want to do a easy and fast fix!
Ranga
-
sorry ranga - but I really wish you'd picked any example but this...formatting and display have nothing to do with logic and rules...(imo)!
**ASCII stupid question, get a stupid ANSI !!!** -
sushila, my point was that database is overloaded with stuff which could very well be done by the front end code or midlle tier!
Just beacuse you have got upto 140 miles in your car's speedometer, it doesn't meant we can do 140!
I hope it is clear now.
-
I thought that today's article had an interesting tidbit that indicates that we've been asking to put more into the database, not less.
http://www.sqlservercentral.com/columnists/sjones/sqlserverspotlightonbrianweckler.asp
"SSC : Why did Microsoft decide to add the Reporting Services engine to SQL Server?
Brian : It was customer feedback really. What we heard was that customers wanted a reporting solution that integrated with their Microsoft data sources, productivity applications, and developer tools. As we had already added OLAP and data mining to the relational database in SQL Server 2000, it was a logical place for reporting."
-
Mike,
Having recently read your reaction to "Logic in the Database", my impression is a few facts might be helpful. You wrote
"he made a case for placing a majority of the business rules and logic in the database"
The article (http://www.sqlsummit.com/articles/logicinthedatabase.htm) actually has a different message and it says:
"There is no single best solution for distributing the logic of a database application, and the choices are many."
The point is that databases today are more than passive data containers. As a rule enforcer, they provide data integrity and ensure consistent behavior. Many databases are accessed by a variety of clients written in different scripting and programming languages (Visual Basic, Javascript, PHP, C#, C++). For example, no matter what scripting or programming language is used to develop clients and servers, they see consistent behavior if they execute sp_accountoverdue.
"most importantly, databases don’t scale."
Scalability is a product of the system architect's skill. It's no longer a limitation of the technology. Consider these facts;
1. It's been 8 years since Microsoft demonstrated SQL Server processing 1 billion transactions in a day (11-14,000 tps).
2. The best performance on TPC-C leader is currently 3,210,540 transactions per minute (53,000+ tps).
3. UPS is doing 1.1 billion SQL transactions per hour on a federated DB2 database.
4. Verizon has an SQL Server database that's 50,747,000,000 rows.
"I can also tell you from experience that over time Ken’s theories on the advantages of this type of model will be proven wrong or at best cumbersome and clumsy."
Microsoft's choice to add the CLR to SQL Server 2005 was not risky business -- letting users embed classes in databases is not revolutionary.
Other SQL vendors (IBM, Informix, Oracle, Sybase) have been there and done that. They've shipped SQL servers that integrate the Java VM, support embedding Java classes in databases, Java stored procedures and UDFs, etc. It's mature technology that's shipped in product releases since 1997.
If this model of extensible databases had proven "wrong or at best cumbersome and clumsy", it's fooled the brain trust of the biggest database vendors. Microsoft isn't the only company integrating the CLR into their SQL servers. IBM and Oracle have done the same.
-
Interesting points, however I would draw a distinction between data integrity, business rules, and UI elements (such as formatting). Experience tells me that whatever is physically allowed in the DB will get in there at some point; and we all know that "the app" isn't the only way of interfacing with the DB.
For my money, the DB needs to have enough logic to ensure data integrity at a minimum. This I would define as part of the base tables.
I tend to prefer a relatively passive UI, and I think one can make a good argument that any functionality that involves only information, and no UI elements, can and probably should be coded into a DB interface layer of stored procs, views, functions etc. This promotes flexibility, reliability, and ease-of-maintenance via the 'single access point' model.
I mean, do we really want to replicate all of our business and data integrity validations at every point where the app touches the DB? And then let all the DBAs and developers skirt that logic via direct backend access? Both are recipes for trouble of you ask me.
Viewing 15 posts - 46 through 60 (of 63 total)
You must be logged in to reply to this topic. Login to reply