SQLServerCentral / Discuss Content Posted by Mike Dillon / Article Discussions / Article Discussions by Author / Where Logic Lives / Latest Posts

RE: Where Logic Lives

Thomas-282729 — Fri, 25 Aug 2006 16:20:00 GMT

IMO, the best way to think about this is using the Fiefdom analogy. Think of a fiefdom as a self-contained system. Messages/data can be sent into the fiefdom and it can send messages/data out. The systems that talk directly to a fiefdom use emissaries to review messages/data to help improve the odds that the messages/data will be accepted. However, when messages/data come into the fiefdom it will re-check the data regardless of who submits it.

Taking this analogy to typical n-layer systems (as opposed to n-tier), you typically have three layers: presentation, middle-layer, and a database. Presentation only talks to middle and middle talks to the database. When data is sent from the presentation layer it is checked before being submitted to the middle layer which checks it again. Why? Multiple applications can submit messages/data to the middle layer. The middle layer cannot assume that those applications have done the proper checks. When the data is submitted to the database, again that information is checked for the same reason the middle layer checks it. The database cannot guarantee that the system sending it data as properly checked its information.

If you can prevent any and all access to the database except through your service layer, then you can indeed put all of your logic, including data integrity rules into your service layer. However, in my experience, this type of guarantee is a fool’s hope. Companies get bought, new systems get built, additional teams build more applications and report generators access the database directly are just some of the scenarios where this lock tight control on the database access is lost. A database should be designed with the assumption that some nutty app developer will try to write crap into the database bypassing any middle layer code.

RE: Where Logic Lives

Thomas-282729 — Fri, 25 Aug 2006 16:17:00 GMT

First, some comments on items in the article or posts...

> I will acknowledge how tempting putting logic in the database is

How do you define "logic"? Do data integrity rules count as logic? Others have broached this very question. A common example that makes the demarcation between integrity and "business rule" fuzzy is referential integrity. One could argue that it is a rule that a parent row must exist prior to adding the child row. I agree with Adam but will phrase it slightly differently. Data integrity rules span all applications and should survive changes in the middle-tier.

> Flexibility decreases as logic increases.....

NO! This really should be "As flexibility increases so does *complexity*." Stating that logic increases or decreases makes no sense.

> What I am saying is that the database can not and should not decide what is valid data.

Taking this concept to its extreme you could end up with multiple applications writing different data into the same column and ending up with crap. App 1 could think that only values FOO and BAR are valid while App2 might only think that CRAP and MORECRAP are valid. While it is true that the applications decide on what valid meaning can be derived from the data, the database should not trust that the application wrote that data correctly. The database should *also* check that data for validity. Those rules should be apply any and all applications otherwise it is different data.

> Databases can be designed to scale out but it is not as easy as

> scaling up, most consultants take the easy road

This is backwards. Databases scale UP easily. Scaling UP refers to putting the database on better hardware. Databases do not scale OUT easily. Scaling OUT means adding additional database servers that work in a cluster or load balancing environment.

RE: Where Logic Lives

KPratt — Thu, 24 Aug 2006 05:42:00 GMT

I disagree with virtually everything he said. I only hope nobody listens or agrees with this article. Geez...

RE: Where Logic Lives

JT Lovell — Wed, 23 Aug 2006 20:40:00 GMT

This thread made me laugh: Say it with me “Databases don’t scale, application servers do.” We could both eat grass too, but it probably doesn't mean we're cows. Any mutli-threaded app can scale. I've crashed far more app servers than database servers due to merely running out of resources though.

Put business logic where it makes the most sense. If you change your business logic as often as you change your shorts, then put your business logic wherever you can develop it the quickest. Often that is a personnel decision, not a technology one. I.e. if you have 3x the .NET developers as SQL developers, then maybe it should be coded in .NET. If the reverse, then code it in SQL. In many cases either place would do just fine. In SQL 2005 the line is even more blury. Please don't design your apps solely based on the possibility that you might someday have to port your app to another database platform. You're just as likely to have to port your GUI to C-flat-plus-minus in a few years.

Keeping your data consistent and clean is done at the database level and that often goes far beyond mere referential integrity. It's also common to put business logic in the database if you're tables are being hit from multiple apps or other database jobs that may not have their own GUI.

Do you really want 3 different development teams coding "similar" business logic for the same set of tables? The devs probably don't code in the same language and there's a pretty decent chance they won't even speak the same language. Even if they did, you're unlikely to have them all developing at the same time, so that one small table change for team A could completely destroy team B and team C's GUIs if they all wrote their own business logic.

I think this author has sat in on one-too-many theory-laden seminars and could use a bit more time in the trenches of database-centric application development. The article is a year old though, so perhaps it was a passing fad.

RE: Where Logic Lives

Jason Hopkins — Wed, 23 Aug 2006 08:57:00 GMT

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.

RE: Where Logic Lives

Ken North — Thu, 22 Sep 2005 22:00:00 GMT

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.

RE: Where Logic Lives

Peter Kryszak — Thu, 25 Aug 2005 12:25:00 GMT

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."

RE: Where Logic Lives

Ranga N — Thu, 25 Aug 2005 12:21:00 GMT

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.

RE: Where Logic Lives

sushila — Wed, 24 Aug 2005 19:20:00 GMT

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)!

RE: Where Logic Lives

Ranga N — Wed, 24 Aug 2005 16:53:00 GMT

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

RE: Where Logic Lives

Randal Rayborn — Wed, 24 Aug 2005 11:03:00 GMT

"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.

RE: Where Logic Lives

Bill Jones-149430 — Wed, 24 Aug 2005 10:34:00 GMT

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

RE: Where Logic Lives

Michael Du Bois — Wed, 24 Aug 2005 04:36:00 GMT

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.

RE: Where Logic Lives

Ola L Martins — Wed, 24 Aug 2005 03:35:00 GMT

Hi alland 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

RE: Where Logic Lives

noeld — Wed, 24 Aug 2005 03:17:00 GMT

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!!!

RE: Where Logic Lives

Adam Machanic — Tue, 23 Aug 2005 18:26:00 GMT

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

RE: Where Logic Lives

David Webb-CDS — Tue, 23 Aug 2005 17:50:00 GMT

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.

RE: Where Logic Lives

Mike Dillon — Tue, 23 Aug 2005 16:47:00 GMT

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.

RE: Where Logic Lives

Adam Machanic — Tue, 23 Aug 2005 16:06:00 GMT

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

RE: Where Logic Lives

sushila — Tue, 23 Aug 2005 16:01:00 GMT

well - talk about coincidences......that alone calls for a beer!

RE: Where Logic Lives

Adam Machanic — Tue, 23 Aug 2005 15:58:00 GMT

One of the main points of my talk is to implement encapsulation and think of stored procedures as black-box APIs

RE: Where Logic Lives

sushila — Tue, 23 Aug 2005 15:55:00 GMT

well - I'm not going to PASS so i think I'll just have mine right away...I know you're going to be one of the speakers Adam...shame I'm going to miss that and all the other names I know so well from this site and otherwise as SQL gurus! Peter...everytime you feel a diatribe coming on...you should take deep breaths and count to 10 - 100 - whatever it takes...unless you're attending PASS and are patient enough to wait for the promised beer!

RE: Where Logic Lives

Peter Kryszak — Tue, 23 Aug 2005 15:51:00 GMT

I would enjoy that. However, I won't be there. My current contract has me too busy, but being active in this conversation has been worth while . Your post just beat mine in, but we are thinking alike on this.

RE: Where Logic Lives

Adam Machanic — Tue, 23 Aug 2005 15:45:00 GMT

Peter,You going to be at PASS? We clearly need to have a beer

RE: Where Logic Lives

Peter Kryszak — Tue, 23 Aug 2005 15:41:00 GMT

Yes.We're seeing more and more distributed teams (some without shared culture if not language), developing in Internet time, contractors that come and go. OOD and SOA emphasize encapsulation and sharing of knowledge-etched-in-code that we can trust has been tested, won't break, and can be used from many different clients.

RE: Where Logic Lives

Adam Machanic — Tue, 23 Aug 2005 15:40:00 GMT

It's not about trusting a team, or teamwork, or anything like that.It's about the fact that we're all human. I don't trust developers, DBAs, management, or even myself, to remember every intricacy every time anything new needs to be developed. And that is the power of encapsulation. No need to trust anyone if you trust the code.

RE: Where Logic Lives

sushila — Tue, 23 Aug 2005 15:34:00 GMT

Well - I trust that I've gotten the point...what is this about trusting the developers anyway...does this mean that everything has to be designed defensively at all times ?!?! are there no "teams" and "teamwork" any more where everything is a collaborative effort of collective heads being put together ?!?!

RE: Where Logic Lives

Adam Machanic — Tue, 23 Aug 2005 15:22:00 GMT

Do you trust the developers that will be working on the application next year?Do you trust the outsourced developers that your company is going to hire in two years?Do you trust that the same logic rules will be implemented the same way when the CTO decides that J2EE is really a better development platform than .NET and wants all new applications developed using it? But wants to keep supporting the old applications?And do you trust that the logic will once again survive when those .NET applications are updated to .NEXT in 5 years time?

RE: Where Logic Lives

Peter Kryszak — Tue, 23 Aug 2005 15:18:00 GMT

That's a very open definition of the data. I think that there is a risk of some other application misusing the trade table if there aren't some higher level constraints placed on inserts to the table. If you can trust your application developers, that's great. If some of the constraints change and have to be reliably distributed out to five or more different applications and you can afford to do that, that's great.It isn't black and white. It depends on the trust in your developers and the cost of distributing logic and constraints rather than centralizing them.

RE: Where Logic Lives

Adam Machanic — Tue, 23 Aug 2005 15:15:00 GMT

They're the same thing. A business rule is a data validation rule if the business is represented in the form of data.Example: Client X has been approved for margin trading. The database ensures that no margin trades are inserted for that client. That's a business rule implemented in the database. Not that applications don't also need to implement that particular rule -- but the database makes sure that no bad data gets in.

RE: Where Logic Lives

Mike Dillon — Tue, 23 Aug 2005 15:11:00 GMT

Hi Peter,

Is what I stated above what you were looking for with regard to a trade

RE: Where Logic Lives

Mike Dillon — Tue, 23 Aug 2005 15:09:00 GMT

My example doesn't require more than one stored proc because there is no business logic in it. The model that each of those trades occurs from or because of is the business logic, not the trade itself. The trade itself is simply data. A symbol, a strike price, time of trade, time of entry, trader and that sort of stuff. The reason that all of the applications can use one stored procedure is because there is no business logic involved. That data means different things to different users/applications depending on the business logic that is applied. What you talk about is not business logic but data validation meaning in this case that you wouldn't insert a symbol with say more than 6 characters. There is a large difference between business logic and data validation.

RE: Where Logic Lives

Peter Kryszak — Tue, 23 Aug 2005 14:29:00 GMT

Mike,How would you define the object in the database in this case; a trade?A "trade" would have different properties and behaviors than an equity, or an option. It may refer to an equity or an option but it is different."business logic" is clearly not agreed upon and needs to be broken down by the natural classifications that Adam has expressed. Also, the objects need to be clearly defined or the classification of the behaviors cannot be accomplished.

RE: Where Logic Lives

Adam Machanic — Tue, 23 Aug 2005 14:28:00 GMT

I see no reason that your example would require more than one stored procedure. You should be able to encapsulate that logic -- somehow -- so that it's data-driven (not that I'm saying that's easy!) But the database should always act as the final arbiter for data. If the database cannot validate the data (in your case, ensure that all of the proper rules were followed), the data cannot be trusted.

RE: Where Logic Lives

Mike Dillon — Tue, 23 Aug 2005 14:24:00 GMT

Adam I agree with everything you just said, except your definition of business logic. Business logic is the logic that is applied to the data in order to obtian the desired results for that particular business at that particular time. If I work for a trading firm that trades options on equities, equities, futures on indexes, options on futures on indexes and so on. The business logic behind how you trade those products is completely different in each case. I don't want to take that into account each time a trade is added to the database. I want one proc that adds the trade for each of them regardless of the business logic associated with trade. It would be great if you could speak of a real world example of your definition of business logic (which is where I think the discussion really lies)

RE: Where Logic Lives

ewilson10 — Tue, 23 Aug 2005 14:09:00 GMT

I should also ask forgiveness if I came across to anyone strongly. Sorry about that folks, I fully buy into the idea of a layer/object shouldn't know what another layer/object is doing. Not that I get it right every time.Although, I'm not kidding when I say scaling is not an issue but just a phantom in my world. I remember when the unit I was in at the time was constantly worried about bottlenecks. Then we'd stress load way beyond anything in our area we could expect and the box would eat up. I don't know this for a fact but if I had to guess the reason why we can feel secure in the hardware, I'd attribute it to two items. First, the total number of records involved from all tables rarely going over 100,000 and never over 200,000. Second, even in those systems that have fundamentally changed the business process the total load on the front end maxes out at a few hunded individual uses a day (24/7 but realistically the web load is from 5AM to 2AM the next night). None of these applications have ever scaled to anything radically beyond our expectations. In fact, it's more likely that a project will be reused on another box in another unit. Just the environment.

RE: Where Logic Lives

Adam Machanic — Tue, 23 Aug 2005 13:55:00 GMT

Actually, that's exactly correct!These rules are none of the application's business. The application shouldn't rely on them in any way (that is to say, the application should trust the database to do the right thing with regard to those rules.) By encapsulating and completely divorcing these rules from the applications (note the plural), flexibility is greatly increased when the rules need to change in some way. And, as we all know, change is the only constant

RE: Where Logic Lives

sushila — Tue, 23 Aug 2005 13:49:00 GMT

mayhap a cavalier attitude but I just can't resist throwing this in...logically then, the business logic rules that this is none of the application's business.......?????????

RE: Where Logic Lives

Adam Machanic — Tue, 23 Aug 2005 13:47:00 GMT

Buy more boxes. How much is your business worth if your data is trash?

RE: Where Logic Lives

Michael R Schmidt — Tue, 23 Aug 2005 13:45:00 GMT

Some business logic can ber *very* complex and therefore can threaten the databases ability to scale. What we may think is easy today may become very complex 1 or 2 years down the road. Or as I have experienced, the rules may change down the road and a simple if else if else (repeat a ferw more times) might turn into a state machine that damn near defies definition.

My question will always be - what do you do when you are using an 8 proc SQL box - and you've optimized as much as possible and you are still have zero headroom for growth? It is not a fun position to be in.