Where Logic Lives

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mdillon/wherelogiclives.asp

  • Mike,

    Can you elaborate a little more about your statement that databases (or DBMSs) don't scale?  I don't really know how the DBMSs themselves are designed so it's a big question in my mind as to how someone like eBay or Amazon or heaven forbid Google designs their database systems.



  • The article implies that logic to maintain integrity should not reside in the database.  I couldn't disagree more.  You either put logic in the database, or you clean garbage out.  It's as simple as that.  I've cleaned up too many databases to buy into this.

    You can argue that logic that transforms data belongs in the middle or presentation tier, and in most cases you would be correct, but logic that guarantees that the database can only contain valid information, whether it include declarative referential integrity constraints, multiplicity constraints, check constraints, or procedural constraints (triggers), is an integral part of the database and as such shouldn't be separate.


    MCDBA, MCSE+I, Master CNE

  • If you've been to a Databases 101 course with a minimally decent teacher the FIRST thing you learn is GARBAGE IN- GARBAGE OUT. You must(is your responsability to) ensure your data integrity as close as possible to the data.

    Yes some business logic that is extremelly intricate may be a candidate for that middle tear but those are not as commom as very well defined rules that are ussually simple and can and in my opinion should be implemented in the Database.

    When you need to perform an operation with a SET of data there is no better software out there than a relational engine.

    Now repeat this with me Application Server Scale at expense of more servers + statless logic + network overhad!!!! there is no win-win solution you need to know what to put where and that's one of the reason we get paid for

    It was an interesting point of view and a food for thought one.

    I just have a different approach



    * Noel

  • Databases do scale and Microsoft would argue that they can scale as well as Oracle, but it takes skill to do this well. As Microsoft SQL Server has risen in capabilities toward Oracle's level, so has the need for better planning and engineering.

    Databases are excellent places to contain business logic; it HAS to be close to the data and it all has to be centralized to be well managed. That's the key idea of OOD and, the next level up, SOA. That's why Oracle had to put Java in its database and Microsoft had to put .NET into its database. These are key enablers of this new mindset.

    The larger and more complex systems being implemented on Microsoft platforms are demanding a new level of sophistication. The database is at the heart of these sophisticated systems.

    DBAs cannot shrug this off and let application developers take all the responsibility for scaling and complexity.

  • Flexibility decreases as logic increases.....true but that's exactly what placing the logic in the db is supposed to do...guard the db from the user who only seems hellbent on trying to throw trash in...

    ....has become a tangled web of different logic for different things that are so specific that they can’t be used for anything else.......why would you want to use a db designed for a specific application with specific requirements, specific purpose ie..custom built be used for anything else ?!

    ...when you need to add features that allow you to do business in another country......surely something like this would be identified even before the db is a gleam in the developer's eye - isn't this why we suffer through the pain of zillions of meetings with user groups who are determined to contradict each other in their requirements for the same application....?!?!

    Mike - when your dad said "..you never have to go out looking for trouble, it will find you on its own.”...I think he really meant that if you don't use all the wonderful features of sql server to your advantage and design the db accordingly, trouble will not only find you, it may well come chasing after you with a club in hand.... - seriously though, are you saying that no logic should reside in the db or that logic should reside in moderation....couldn't quite get a sense of where you stand in all this?!?!?!

    **ASCII stupid question, get a stupid ANSI !!!**

  • Totally agree.  Business logic should stay with the application code.  This allows for total flexibility.  For different situation, just compile in a different class, or use a different xml file, or a different property file.  Database is for data.  Leave it that way.

  • So your programmers never fail to validate data and there's no way that a new data import procedure can screw up the data????????

  • jkli,

    That's ok for small, static, narrowly targeted applications. That has been Microsoft's heritage and there will continue to be such applications.

    However, Microsoft is targeting larger, more complex applications that serve broader audiences in large enterprises. Corporations are demanding such applications be hosted on Microsoft technology for cost reasons. The DBAs and developers that can step up to these will be much more successful than those working on small systems.

    Databases are not just for data anymore.

  • Peter,

    I happen to work on a very large, enterprise project and through my over 20 year career worked on many large, enterprise projects.  Exactly for the reason of enterprise projects, business logic should stay with the application, as you never know which part of the business logic requires changing. 


    With object oriented languages like c++, java, c#, as well as others, we can use design patterns to handle complex business logics, place them in different packages for different situations.  The new sql server has ability to use c# to write stored procedures.  However, it is not easy to apply design patterns as it is still stored procedures,  and to use packaging to allow for the flexibility of changing business logic on the fly.


  • I am sorry if you believe that the article implies that. It wasn't intended to. I am not asking you to buy into anything and I too have cleaned out more databases then I would like to recall because of a lack of referential integrity.

    What I am saying is that the database can not and should not decide what is valid data. What is valid data one day may not be the next depending on the needs or wants of the application. Once the application decides what is valid, then yes, the database should be used as a tool to help maintain the integrity of the data.

  • Because for years there has been one application and one database,

    encapsulating the data validity and integrity rules in the application has worked successfully. There will continue to be opportunities for these designs.

    However, the requirements and resulting architectures are expanding. There are now multiple applications being connected to multiple databases in less tightly coupled ways. The validity and integrity rules must be stored close to the data if there is any chance to manage them cost effectively in this new environment.

    Certainly, some of the business logic will be encapsulated in web services that may or may not be closely coupled to the database, but there is now no reason not to keep it close to the database. The design will require careful thought but that's true for any application. Also, the DBAs will have to think more about applications and not just storage management.

  • Much like the post before, you are confusing business logic with integrity. I agree on the notion of garbage in garbage out, however who are you to decide what is garbage. You can't. The business rules decide what information is important from day to day and those business rule change on the whim of a customer or CEO or News conference or just about anything else the current industrial climate may provide.

    As for the set of data, I don't think anyone would argue that getting a set of data out of the database belongs anywhere else. It is what you do with that data that matters. One day you might do something as simple as sorting the data, then next you may have to use the data set in a complex mathmatical formula for derivite trading. You can't do the latter in the database so the data that is in the database shouldn't be specific to that action. Get the set you need and manipulate it programmatically.

    As for the comment on overhead, all of those things are cheap and if your application and business logic live in the correct place, then scaling is easy. Take a portion and run here and a portion and run there. You can scale to the hardware available and you have many options for handling network overhead. You can do none of that with you logic living in the database.

    Again, in a small scope with limited data, your approach works well. None of these things are issues in a small shop setting.

  • This is not a microsoft V. Oracle issue. It is a fundamental design issue. Oracle doesn't handle business logic any better than Microsoft or

    Sybase or IBM.

    The "new" mind set is wrong. I was just at the SQL2005 road show and I asked the developer hosting that tract what the idea behind MS integration of the CLR into the database was. Are they trying to move business logic into the database. His response was that he could now create an application that would run with just the database and IIS using the xml fetures of SQL 2005. His next statement of course was just because you can doesn't mean you should.

    Tell me how excellent a place the database is for business logic when you company says they are switching from Oracle to MSSQL or to IBM or any other. The real world dictates a level of flexabilty that business logic in the database does not support.

    Please don't think I am encouraging DBAs to shrug off their responsibility. In fact, it is just the opposite. A DBAs responsibiblity to help the developer where the line between business logic and database functionality resides.

  • This sounds like someone is trying to argue one solution for the whole world.

    I'm in the final stages of redesigning a system I inherited and I have to say using the SQL Server for holding the business logic was a saving grace that will meet the future needs of the project and is very flexible. Why thank goodness for SQL? The project started with an MS Access front-end that is still the primary interface. Since then two differnet web sites are using the data and another unit is pulling information out. I should point out that I'm in a very decentralized environment. We don't move from one data server to another. Instead we have anything and everything. Most of the enterprise runs either Oracle, MS SQL, or MySQL but many flavors exist beyond that.

    The article was also a good example of an interesting trend. Objrect oriented programming, and even more interestingly design patterns, refactoring, power porgramming, and their ilk, bring up a lot of good ideas. T-SQL is spagetti language but it doesn't mean that we can't apply a lot of the new ideas to what we do. Even if a stored procedure is going to do only one job, take the extra time to avoid hard coding, ask what is really being asked of the stored procedure, apply some of the refactor concepts to the code. My current project has grown as I suspected it might and I'm finding it's very flexible. Even where I've been suprised the stored procedures have been ready to take on the changes (my one complaint is the inability to handle nested insert execute statements).

    I'm hoping to expand the scope of one of my systems to the whole enterprise. If and when that time comes, or if another large-scale project comes along, I'll be back in .NET C# or Java and trying to apply all the best practices I can along with other programmers to create a long-lived project. Also, with SQL 2005 I'll start to look for opportunities to move business logic in to C# or Java. But not this current project. The SQL Server for the business layer has been excellent, it's been flexible, and it will scale to cover the range of possibilities.

    One size fits all, feh.

    Everett Wilson

Viewing 15 posts - 1 through 15 (of 63 total)

You must be logged in to reply to this topic. Login to reply