#272097 - 13/12/2005 00:58
Business Logic In The Database?
|
carpal tunnel
Registered: 14/01/2002
Posts: 2858
Loc: Atlanta, GA
|
I know I've broached this particular subject before when talking about using Stored Procs to access data, but I'd really like to address this head on: Is the databse the proper place to put business logic?The reason I'm asking is because I'm going to be responsible for designing an arhcitecture for a new application, and I'm getting assulted on both sides over this question. My boss (who no one on the team respects) is insistent that all business logic should go in the database. My coworkers, one of whom will be on my team and is very passionate, insist that databases are for data persistance only and the business logic does not belong there. No matter what stance I take I get hammered- problem is I disagree with them both. I don't see how anyone can make this catagorical statement that BL belongs one place or the other. And certainly a DB is more than persistence- at the least it should take some responsibilty for maintaining the integrity of the data. It seems to be a relgious debate, which I try to stay away from in software design, preferring to take things on a case by case basis. And now I'm taking a SQL Server 2000 programming course and M$ own best practice (take that for what it's worth) says "Business Logic should be in the database". Conisder the source, though.  So I go to the internet with everyone decrying everyone else's architecture claiming that the "other side" knows absolutely nothing. Thing is, I don't know any of these people and have no gauge as to how much weight to put on their arguments. Here, though, there are people I respect. So I put it to you, where does the business logic go? Now, my perspective (as I've said) is "it depends", so here are some details about the project: 1. It will be client server, so no middle tier for the BL. 2. The database will likely be SQL Server 2005 3. There will be about 25 users max, all internal to the company 4. Our team is quite inexperienced with T-SQL. I'm not looking for an answer really as much as some intelligent, non-religious perspectives. Any comments along this vein would be greatly appreciated.
_________________________
-Jeff
Rome did not create a great empire by having meetings; they did it by killing all those who opposed them.
|
|
Top
|
|
|
|
|
#272098 - 13/12/2005 02:57
Re: Business Logic In The Database?
[Re: JeffS]
|
carpal tunnel
Registered: 25/12/2000
Posts: 16706
Loc: Raleigh, NC US
|
I'd be inclined to say that logic belongs in the application. First, what is the compatibility between databases? I'd never question the likelhood of someone telling you that you can't use SQL Server anymore and have to use Oracle from now on. How difficult would the logic being in the database make that? Second, it's a lot easier to change a database than an application. That sounds like an argument to keep it in the database, but it's not, as I believe that will lead to improperly tested changes.
But I'm no expert.
_________________________
Bitt Faulk
|
|
Top
|
|
|
|
|
#272099 - 13/12/2005 04:24
Re: Business Logic In The Database?
[Re: JeffS]
|
carpal tunnel
Registered: 10/06/1999
Posts: 5916
Loc: Wivenhoe, Essex, UK
|
Quote:
And now I'm taking a SQL Server 2000 programming course and M$ own best practice (take that for what it's worth) says "Business Logic should be in the database". Conisder the source, though.
Did whoever told you that provide some authoritive proof that that is Microsoft's own best practice ?
I'm sure it was there best practice a few years ago, but every indication from them over the last few years (for example from their recent sample code) leads me to believe that their position has shifted.
Quote:
2. The database will likely be SQL Server 2005
4. Our team is quite inexperienced with T-SQL.
One interesting thing to note is that with SQL Server 2005 you can use C#, VB.NET and other .NET managed code instead of T-SQL for stored procedures. I haven't used it yet, so I have no idea how well it performs.
http://www.codeproject.com/cs/database/CLR_Integration.asp
Of course using managed code stored proceedures is the perfect way to lock yourselves into Microsoft's database forever...
And for the record, no the database is typically not the right place for business logic.
Edited by andy (13/12/2005 04:30)
_________________________
Remind me to change my signature to something more interesting someday
|
|
Top
|
|
|
|
|
#272100 - 13/12/2005 06:23
Re: Business Logic In The Database?
[Re: andy]
|
old hand
Registered: 28/12/2001
Posts: 868
Loc: Los Angeles
|
Quote:
One interesting thing to note is that with SQL Server 2005 you can use C#, VB.NET and other .NET managed code instead of T-SQL for stored procedures. I haven't used it yet, so I have no idea how well it performs.
Not particularly well. It's very limited in what it can do, awkward in implementation, and damn slow from what I have seen. MS advises using it sparingly.
I don't even see how you can do good BL in the database. Your only real tool is SQL, and that too is very limiting and slow. Too many things require cursors and tons of temporary tables, both of which suck and end up being massively convoluted. You end up doing all the processing on one computer, instead of spreading it around to the client computers. You get a complete lack of UI interaction, because every thing needs to be round-tripped to the database. Not to mention that if your business objects and data tables are exactly the same, you are probably doing something wrong.
There is no reason for their not to be a middle tier just because it is client-server. Logical tiers have only a little to do with the hardware setup, they are about organization of code. For instance, I think BL should be in one place, the more scattered it is the harder it is to maintain, debug, port, and scale. A separate logical tier, even if that tier resides on the client or server machine, is significantly easier to deal with than just sticking your BL in the UI or database.
_________________________
Ninti - MK IIa 60GB Smoke, 30GB, 10GB
|
|
Top
|
|
|
|
|
#272101 - 13/12/2005 07:21
Re: Business Logic In The Database?
[Re: wfaulk]
|
carpal tunnel
Registered: 14/01/2002
Posts: 2858
Loc: Atlanta, GA
|
Quote:
Second, it's a lot easier to change a database than an application. That sounds like an argument to keep it in the database, but it's not, as I believe that will lead to improperly tested changes.
You have no idea how funny, in a sad sort of way, this statement is to me. My boss, who seems to be the self appointed advocate for all things sql, goes around giving lip service to SDLC, making sure that all of our code is well documented before it is released/changed (as it should be), but makes changes to the database code without even having any sort of design doc at all. She is also very suprised when her code breaks.
Of course her development is faster with SQL than any of the application developers are- it's easy to write fast, poor code without adhering to the SDLC.
Can you tell I'm bitter about this?
As to your first point- I seriously doubt we'd move to Oracle. However, our one true "SQL Developer", who is my boss's hire, uses your same logic to argue against putting BL in the database.
_________________________
-Jeff
Rome did not create a great empire by having meetings; they did it by killing all those who opposed them.
|
|
Top
|
|
|
|
|
#272102 - 13/12/2005 07:31
Re: Business Logic In The Database?
[Re: andy]
|
carpal tunnel
Registered: 14/01/2002
Posts: 2858
Loc: Atlanta, GA
|
Quote:
Did whoever told you that provide some authoritive proof that that is Microsoft's own best practice ?
I'm sure it was there best practice a few years ago, but every indication from them over the last few years (for example from their recent sample code) leads me to believe that their position has shifted.
This was on a slide in an official training course for programming SQL Server 2000, a course that was just updated by M$ within the last three months. And they take these slides pretty seriously- I asked if I could get a copy and the instructor said M$ won't give them out.
Also, I'd say the inclusion of the CLR in 2005 and the ability to make 2005 a HTTP listener also support this claim (though the inclusion of those features is certainly not definitive).
Quote:
One interesting thing to note is that with SQL Server 2005 you can use C#, VB.NET and other .NET managed code instead of T-SQL for stored procedures. I haven't used it yet, so I have no idea how well it performs.
Apparently, it's not good for raw data access, as it has to go through ADO.NET, however it is much faster for computational logic. I think it's a pretty cool option, though, and will at least let us leverage common utilities between the front and back end.
Quote:
Of course using managed code stored proceedures is the perfect way to lock yourselves into Microsoft's database forever...
Yeah, I think if I were a DBA I'd not want assemblies anywhere near my database. Of course, we don't have a DBA and programmers (for whatever reason) don't seem to think of such things.
Quote:
And for the record, no the database is typically not the right place for business logic.
Thanks for wheighing in . . .
_________________________
-Jeff
Rome did not create a great empire by having meetings; they did it by killing all those who opposed them.
|
|
Top
|
|
|
|
|
#272103 - 13/12/2005 08:00
Re: Business Logic In The Database?
[Re: ninti]
|
carpal tunnel
Registered: 14/01/2002
Posts: 2858
Loc: Atlanta, GA
|
Quote:
There is no reason for their not to be a middle tier just because it is client-server. Logical tiers have only a little to do with the hardware setup, they are about organization of code. For instance, I think BL should be in one place, the more scattered it is the harder it is to maintain, debug, port, and scale. A separate logical tier, even if that tier resides on the client or server machine, is significantly easier to deal with than just sticking your BL in the UI or database.
Absolutely agree on all points here, with the one exception that I'll put some BL in the database if it makes a huge difference from a performance perspective- i.e. those operations that are a LOT of database access which would require a lot of round trips between the client and database. However, with such a limited user base this should be a fairly limited occurance, as the only performance that matters is what the users experience.
I always get a chuckle out of my boss's argument that stored procs are faster because of their cached execution plans. It's true of course (though did you know that in-line SQL is also cached so if it's run a second time you get the same performance boost?), but this performance boost (which doesn't really apply to BL anyway, since BL is always going to be in some cached/complied block of code) isn't going to matter from the perspetive of our users, who are clicking buttons and waiting for the software to respond. I'd say in this enviornment, software maintainability is a lot more important than raw speed. This is off topic, though, since you can use stored procs all day and still have your BI in a logical middle tier.
To that last point, my boss also argues that SQL code is "more maintainable", without really saying what that means. I believe she means that you can change it without deploying a new executable, which is a really silly argument since the users are all pointed to the same executable on a shared drive. It's just as easy to recompile an executable and deploy it than to make changes to a production database, and less dangerous IMHO- not to mention Bitt's point about that danger of easy changes still stands. And then from the standpoint of coding, there is no way that T-SQL is "more maintainable" than C# or any other front end, object oriented language.
Oh, and regarding a logical vs. physical middle tier, I understand the difference. However, my boss views a logical middle tier as putting the BI in the database. In fact, she said to me rather directly that when the other developers on my team advocated using a logical middle tier on the client that "they don't understand three tier architecture". Nice, hunh?
Appreciating these perspectives, all.
_________________________
-Jeff
Rome did not create a great empire by having meetings; they did it by killing all those who opposed them.
|
|
Top
|
|
|
|
|
#272104 - 13/12/2005 14:39
Re: Business Logic In The Database?
[Re: JeffS]
|
carpal tunnel
Registered: 19/01/2002
Posts: 3584
Loc: Columbus, OH
|
Any chance you'll ever have multiple front-ends for this? For example a web front end and a executable front-end? Or possibly that you'll want it to work accross very limited bandwidth like a dedicated T1 or even a Dial-Up VPN?
_________________________
~ John
|
|
Top
|
|
|
|
|
#272105 - 13/12/2005 15:41
Re: Business Logic In The Database?
[Re: JBjorgen]
|
carpal tunnel
Registered: 30/04/2000
Posts: 3810
|
Most of this has already been discussed, but I'm of the camp that BL only goes inside the database if it's the only way to hit your performance targets. I suppose if there's a non-SQL interface that's superior in some fashion for your BL, then that might also be a valid reason to go inside the database.
Reasons to *not* go inside the database are worth making to your boss. For starters, injecting any code creates the opportunity for things to go wrong in spectacular fashion. For example, your database may or may not need to be restarted if your injected code goes into an infinite loop or otherwise freaks out. When you keep things separate, it's much easier to upgrade (or reboot) one component or the other. Of course, you can use caching to soften the hit of talking to the database, but that gets more complicated if you want to distribute your BL across multiple servers. Still, it's easier to distribute BL than to distribute a database.
|
|
Top
|
|
|
|
|
#272106 - 13/12/2005 15:57
Re: Business Logic In The Database?
[Re: JBjorgen]
|
carpal tunnel
Registered: 14/01/2002
Posts: 2858
Loc: Atlanta, GA
|
Quote:
Any chance you'll ever have multiple front-ends for this? For example a web front end and a executable front-end?
Possible, but only for some of the functionality. Not enough to justify using a physical middle tier for BL in the client/server portion. Probably the better option is to create a common BL library that both the web app and client app can use. Or that's what I think.
_________________________
-Jeff
Rome did not create a great empire by having meetings; they did it by killing all those who opposed them.
|
|
Top
|
|
|
|
|
#272107 - 13/12/2005 15:59
Re: Business Logic In The Database?
[Re: DWallach]
|
carpal tunnel
Registered: 14/01/2002
Posts: 2858
Loc: Atlanta, GA
|
Quote:
Reasons to *not* go inside the database are worth making to your boss.
Excellent points, thanks. Don't think my boss is really open to arguments, but I am, and ultimately I belive (hope?) it's my call.
_________________________
-Jeff
Rome did not create a great empire by having meetings; they did it by killing all those who opposed them.
|
|
Top
|
|
|
|
|
#272108 - 13/12/2005 20:00
Re: Business Logic In The Database?
[Re: JeffS]
|
carpal tunnel
Registered: 08/07/1999
Posts: 5549
Loc: Ajijic, Mexico
|
It seems to be a relgious debate, which I try to stay away from Jeff, I'm sure you can imagine why this got a chuckle from me.  tanstaafl.
_________________________
"There Ain't No Such Thing As A Free Lunch"
|
|
Top
|
|
|
|
|
#272110 - 14/12/2005 02:00
Re: Business Logic In The Database?
[Re: JeffS]
|
pooh-bah
Registered: 31/08/1999
Posts: 1649
Loc: San Carlos, CA
|
One more reason for the BL to NOT be in the DB is that you can get away with a different class of developer. I suspect that it is going to be easier to find developers who are used to a model of BL outside the database and I'd be leery of allowing a junior developer to write in DB code as there are more opportunities to massively screw things up.
Put me down as a "logic only goes in the DB if it is there for data integrity or performance" vote.
-Mike
|
|
Top
|
|
|
|
|
|
Previous Topic
Index