Page 3 of 3
Re: Business Logic & Stored Procedures
Posted: Thu Feb 12, 2009 3:46 pm
by VladSun
pytrin wrote:Simple, create an API. All database access should run through the same database abstraction code, which should be aware of validation and relationships.
Exactly! I think of stored procedures more like an lightweight API to the DB layer.
pytrin wrote:Separate applications should interface with the primary application API instead of directly with the database. It's also much more secure if they're on separate servers (I wouldn't like to open my database to outside access).
No

The first application is not aware of the changes made after it is created. And in many cases one doesn't have access to the source code, which is also to be mentioned.
Re: Business Logic & Stored Procedures
Posted: Thu Feb 12, 2009 3:55 pm
by Eran
No access to the source code is needed. I'm talking about a RESTful API over http. This is much more maintainable than allowing disparate applications their own access to the same database.
Re: Business Logic & Stored Procedures
Posted: Thu Feb 12, 2009 3:56 pm
by josh
That would be coupling an abstraction to it's implementation. Stored procedures are harder to debug, less portable, etc.. ( I sound like a broken record). It also fragments business logic across your tiers which makes the entire system more bug prone and less maintainable. I don't see how you can keep mixing up the terms structure & logic. One need not de-normalize a system to keep logic out of the data store. It's called layered architecture for a reason, if you don't like it don't use it.
Re: Business Logic & Stored Procedures
Posted: Thu Feb 12, 2009 4:31 pm
by VladSun
Don't get me wrong
In a 50'000+ PHP code lines, maybe 5'000+ SQL query lines and 40'000+ C# code lines, my colleague and me used only 10 stored procedures for operating with data and 50 stored procedures for reports.
We agreed that using stored procedures for everything is really bad, but we also agreed that there are some cases when we could put a small part of the business logic (if it can be really considered as a business logic) into SPs.
While I think you will agree that SPs for reports are OK, the other SPs manage the integrity of the data for statistical purposes - i.e. instead of deleting a record and related ones we delete some of the relation records and mark the others as deleted (i.e. indeed it's an UPDATE operation). That's because we can't really delete a data because it absence will reflect onto the reports. We could do it even with triggers, but that would put "business logic" into every table declaration.
So, I'm not a SP-user, simply I didn't like some of the statements in this thread.
I do agree with most of the things you (repeatedly

) mentioned as a whole, but I don't agree with some parts of them.
I'll keep using foreign key constraints, unique constraints and everything else provided by the DB engine in order to keep the integrity of the DB data, so even a buggy application will not going to ruin its integrity

Re: Business Logic & Stored Procedures
Posted: Thu Feb 12, 2009 4:33 pm
by Weirdan
VladSun wrote:I asked for examples just because I couldn't find (from your words and articles you pointed to) *any* suitable ones for *any* of these features.
But these features still exist, they are under hard development and they have to be used somehow. But ... how, where, when?
The short answer: they should be used when business logic must be encapsulated in the database
The long answer: There are cases when it's better to move business logic to database because moving it out of the database would lead to business logic duplication. This is the common scenario when data is shared between multiple software systems.
Consider a following scenario: a business have an offline shop and on-line e-commerce site, which trade same products, from the same warehouse. Essentially they are two faces of a single shop. There's a discount scheme where customers have personal discounts and there's a sale day held every Friday (beer is two times cheaper on those days

). Discounts are stackable, of course. Discount calculation logic is better implemented as a stored procedure in this case, because if there's a bug in it, there would be only one place to fix it (otherwise you'll have to fix it both in POS software and in site code, which are, most probably, implemented in different languages and therefore cannot share models). To avoid duplication in such cases you basically have two options: either implement this logic as a separate software package and make dependent packages call it (it could be a library or webservice or something else) or move this logic to the database (and make dependent packages call it). These options do not differ much, IMO.
To sum up, duplicated code is worse than a code in the "wrong place".
Re: Business Logic & Stored Procedures
Posted: Thu Feb 12, 2009 4:37 pm
by Weirdan
Oops, my previous post took too long to write and similar arguments have been expressed already. I still like my example more though =)
Re: Business Logic & Stored Procedures
Posted: Thu Feb 12, 2009 4:38 pm
by VladSun
Cool, Weirdan

Now you are the one to be attacked, not me

I already mentioned the "multiple application-single DB" case, and also "the buggy application - iron DB design" but ...
Edit: Yes, you've noticed it

Re: Business Logic & Stored Procedures
Posted: Thu Feb 12, 2009 4:39 pm
by josh
@weirdan, I'd argue that's what middleware architecture is for. Enterprise integration patterns by their very definition kinda conflict architecture tho, I think we can all agree an atomic integrated system outweighs multiple legacy systems, but in the business / real world compromises have to be made.
@vladsun I've acknowledge there's exceptional situations. I think its important to realize too what the difference is between data integrity ( structure / shape ) and behavior. Tupples and their relations are not subsumed by the definition of logic, IMO.
Re: Business Logic & Stored Procedures
Posted: Thu Feb 12, 2009 4:44 pm
by VladSun
josh wrote:I think its important to realize too what the difference is between data integrity ( structure / shape ) and behavior. Tupples and their relations are not subsumed by the definition of logic, IMO.
I'm saying that "data integrity ( structure / shape )" limits the possible "behavior" - that's all

.
Re: Business Logic & Stored Procedures
Posted: Thu Feb 12, 2009 4:50 pm
by Weirdan
josh wrote:@weirdan, I'd argue that's what middleware architecture is for.
Nor I deny that. The implication of my post was that modern databases had moved past the point of being dumb storage engines into that very class of middleware software you've mentioned.
Re: Business Logic & Stored Procedures
Posted: Thu Feb 12, 2009 6:54 pm
by josh
Weirdan wrote:Nor I deny that. The implication of my post was that modern databases had moved past the point of being dumb storage engines into that very class of middleware software you've mentioned.
Nor I deny what you're saying, but IMO its still harder to debug, less portable, etc.. If the end design goal were to minimize fragmentation of business logic across the tiers, taken to it's logical conclusion 100% of business logic would be in the database or 100% in the business tier. Personally I'd rather use 1 common implementation language so some day it would ease the merging of the 2 branches into 1 common code base, but I readily admit not being an expert on system integration by any means so that's all I say on that topic.
VladSun wrote:I'm saying that "data integrity ( structure / shape )" limits the possible "behavior" - that's all

.
You've officially lost me now...