Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««2829303132

There Must Be 15 Ways To Lose Your Cursors… Part 2: Just Put It in a S Expand / Collapse
Author
Message
Posted Tuesday, December 13, 2011 8:35 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 8,296, Visits: 8,750
Coming back to this after much delay, because I came across it as a reult of someone's reference to RBarry's article and I think it's an important issue.

Thomas-282729 (7/24/2010)
Much like Alex's article, I think you are confusing data integrity with business logic which are both needed for any database driven solution. Check constraints and uniqueness constraints relate to the integrity of the data.

No, they are clearly business logic. If I have a business rule that says "any salesman can be selling exactly one product class" that generates a uniqueness constraint on the two columns SalesPersonID and ProcuctClassId in whatever table tells me which is the product class sold by each salesman. If my business rules allows each salesman to sell products belonging to several product classes, such a constraint can not be imposed - the business rule that the constraint represents does not exist, so the constraint must not exist. You are proposing an utterly false distinction here - any question of "data integrity" boils down to a question of what states of the data are permitted by the business rules; we impose constraints, and normalise, in order to ensure that states not permitted by the business rules are unreachable, and that is the sole meaning of the phrase "data integrity": states which shoule be unreachable states (whose unreachability is determined by the business rules) must not be reachable. So data integrity rules are a particular subset of the business rules, not something separate from business rules, and declaring that the database must not implement business rules amounts to declaring that you are not permitted, in the database, to ensure data integrity - which is, in my view, absolute claptrap.

Rules such as "in march, sales from reps with a certain sale volume threshold and for products of certain types should have an interest rate discount based the users credit score accounting for a series of values some of which come from services" are difficult to implement and properly test in a database.

So there are some business rules that should probably not be in the database; how did you achieve the amazing leap from there to "there are no business rules that should be in the database"?

I never said that absolutely no business logic should be in the database. Instead, I suggested that if you are required to implement an iterative solution in the database using cursors, it does not belong in the database.[ The database is not the end-all-be-all repository for business logic. There are some forms of logic that simply do not belong in the database. Sometimes the answer for how to do something in T-SQL is not to do it T-SQL.

That seems to be a long step away from what you said earlier. It's something I agree with - I'm not interested for example in handling something in T-SQL that would be more appropiately handled in Fortran. But I still regard any statement that the database is not the place for business logic as nonsense, since there is oftrn a large amount of business logic (even if you want to call it something else, like "data integrity logic", and pretend it isn't business logic despite the very concept of data integrity being meaningless unless it is defined as banning states forbidden by business logic) that absolutely must be implemented in the database if we are to have clean bug-free systems.


Tom
Post #1221308
Posted Tuesday, December 13, 2011 10:56 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, March 14, 2014 9:12 AM
Points: 89, Visits: 478
Let's step back for a second. The point I have made multiple times is that if you have to resort to T-SQL cursors, that is logic that almost assuredly does not belong in the database. The declarative features in SQL Server are such that there shouldn't be situations (i.e., they should be extraordinarily rare)where you must resort to cursors and have that logic in the database.

The only reason you consider the term "business logic" vague is that you are using it mean everything and the kitchen sink. As far as I can tell, by your reckoning, any constraint of any sort is business logic. I do not consider check constraints to be "business logic" even if it is a rule dictated by the business. Perhaps another term is needed to differentiate rules used to ensure the correctness of the data as opposed to rules related to facilitating of a business process. I'm calling the former data integrity and the later "business logic". There is no absolute demarcation between the two, however, a good point of reference is that if you must resort to using iterative solutions within a set based system (the database), the odds are good it does not belong there.

Thus, if one differentiates forms of business logic, between logic for data correctness and logic for business processes, the yes, the later does not belong in the database IMO. My statements throughout are consistent unless one extrapolates the term "business logic" to mean any decision point anywhere in the system no matter how small in which case the term itself is meaningless and I doubt that is the case with the other posters.
Post #1221340
Posted Wednesday, December 14, 2011 8:20 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 8,296, Visits: 8,750
Thomas-282729 (12/13/2011)
I do not consider check constraints to be "business logic" even if it is a rule dictated by the business.

Amazing. Rules dictated by the business are not business logic. That is a pretty heavy abuse of the language, I think.

We agree clearly that there is some business logic which doesn't belong in the database; but I can't get my head around the idea that things that enforce business rules aren't business logic. To me it just doesn't make sense to claim that enforcement of a rule derived from the business, possibly from an overiding policy decision at the highest level in the enterprise, is not business logic.


Tom
Post #1221738
Posted Thursday, December 15, 2011 10:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, March 14, 2014 9:12 AM
Points: 89, Visits: 478
L' Eomot Inversé (12/14/2011)
Thomas-282729 (12/13/2011)
I do not consider check constraints to be "business logic" even if it is a rule dictated by the business.

Amazing. Rules dictated by the business are not business logic. That is a pretty heavy abuse of the language, I think.

We agree clearly that there is some business logic which doesn't belong in the database; but I can't get my head around the idea that things that enforce business rules aren't business logic. To me it just doesn't make sense to claim that enforcement of a rule derived from the business, possibly from an overiding policy decision at the highest level in the enterprise, is not business logic.


Perhaps it would help if you defined what *isn't* "business logic" that belongs within the confines of a solution for a business. As I said earlier, you are using "business logic" to be equivalent to the word "smurf". It can mean anything and everything that is even remotely related to any decision whatsoever in the business and that type of broad, vague definition does not help to understand what sorts of logic belong in the database and more importantly, what forms of logic do not belong in the database. If any and all logic is "business logic" then any form of logic makes sense to put in the database and that clearly is not the case. Again, perhaps it would help to avoid the phrase "business logic" and instead think in terms of logic that relates to data correctness and logic that revolves around a business process. They both relate to a solution used for a business but only one generally belongs in the database.
Post #1222620
Posted Thursday, December 15, 2011 2:11 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 8,296, Visits: 8,750
Thomas-282729 (12/15/2011)
Perhaps it would help if you defined what *isn't* "business logic" that belongs within the confines of a solution for a business. As I said earlier, you are using "business logic" to be equivalent to the word "smurf". It can mean anything and everything that is even remotely related to any decision whatsoever in the business and that type of broad, vague definition does not help to understand what sorts of logic belong in the database and more importantly, what forms of logic do not belong in the database.

Yes, it is necessary to understand what business logic belongs in the database and what business logic doesn't. Understanding that doesn't however involve declaring that some things which clearly are business logic (for example the decision to so organise the sales team that each salesman can deal in only one product line, or equally the decision not to make the organisation like that, or the decision to leave the question open) are not business logic just because they have implications for constraints which belong in the database - changing the meaning of "business logic" just in order to allow the nonsense statement that the database must contain no business logic to appear to be true.
If any and all logic is "business logic" then any form of logic makes sense to put in the database and that clearly is not the case.

Who has suggested that any and all logic is business logic other than yourself? And whence the enormous nonsequitur that if any and all logic were business than it would be appropriate to put all logic in the database - so far as I know, no-one has made the claim that all business logic should be in the database. Are you perhaps playing at putting up ludicrous strawmen to enable yourself to appear to logical in opposing a position which is nothing like those strawmen? I could take your new suggestion as meaning that you want to assert that since all logic is business logic the database should contain no logic of any description, but I don't actually believe that you want to assert that. If you are claiming that I have asserted that you are claiming something which clearly is not true (as should be clear from reading any of my posts). To take an extreme case I am prepared to assert, for example, the logic used by Godel to demonstrating the consistency of the generalised continuum hypothesis with a commonly used axiomatisation of set theory and by Cohen to demonstrate its independence of those axioms could never under any circumstances be considered business logic.
Again, perhaps it would help to avoid the phrase "business logic" and instead think in terms of logic that relates to data correctness and logic that revolves around a business process. They both relate to a solution used for a business but only one generally belongs in the database.

The problem that the question whether data is correct is a business problem. It can only be ascertained or tested by examining business rules and can only be ensured by enforcing business rules. The business logic embodied in those particular business rules should be in the database, and I don't see any point at all in claiming that that logic is not business logic. Indeed if it were not business logic, why would it differ from one business to the next, as it clearly does?

Here's a simple rule for determing what business logic must be implemented in the database:
If a business rule can be enforced on the data using a combination of key constraints and/or domain constraints (in SQL as opposed to in relation algebra that amounts to primary key constraints, uniqueness constraints, foreign key constraints, not null constraints, qualifications of a column's datatype as to precision, scale, size, or collation, and check constraints) with reasonable modification (normalisation) of schemata to achieve this, then that should be done; the logic of the business rules enforced in this manner should therfor be implemented in the database. It should also be implemented in the application logic, including the front end, since it is preferable to have things work without the application having to cope with constraint violations reported by the database, and for the user interface to avoid as far as reasonably possible presenting the end user with options that would violate the business rules (and the extremely rare cases when avoidance isn't reasonably possible giving the user instant feedback if there's a problem, rather than waiting for the database to signal a problem).

Application logic other than the above in the database? Well, I don't like exposing the database to ad-hoc queries, nor indeed exposing the schemata to the applications (I want maximal decoupling for modularity). So I prefer the applications to be forced to call stored procedures and have no permissions to read or write any table or view other than by calling the provided stored procedures. Definitely that should be so organised that it puts as little extra application logic (application logic over and above that which enforces data integrity) into the database (in the form of stored procedures) as can reasonably be achieved, but it almost always will put extra application logic into the database - you don't want to do the joins at a layer above the database, and I guess you would claim that application logic embedded in SQL code produced by the application as opposed to in SQL code contained within the database is not in the database. But I won't claim it's essential to go that route: although it's certainly not possible to build an equally secure system any other way not all applications require that much security, and using parametrised SQL queries in an above database data-access layer is fully secure against injection attacks, which is enough for most people; and such a data-access layer only breaches modularity by exposing the schema if it is controlled as part of the app as opposed to as if it were part of the database.

But perhaps the fundamental problem with the "no business logic in the database" mantra is that it's used by many DBAs, DB developers, and DB designers as an excuse for making no attempt to understand the business, resulting in catastophically poor outcomes of development projects because without understanding the business they can't understand what data states are forbidden by the business logic or what data volumes are implied by technicalities of teh business and as a result can't design schemata that will provide data integrity and also can't estimate within several orders of magnitude what sort of storage is needed.


Tom
Post #1222766
Posted Thursday, December 15, 2011 11:49 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, March 14, 2014 9:12 AM
Points: 89, Visits: 478
L' Eomot Inversé (12/15/2011)
Thomas-282729 (12/15/2011)
Perhaps it would help if you defined what *isn't* "business logic" that belongs within the confines of a solution for a business. As I said earlier, you are using "business logic" to be equivalent to the word "smurf". It can mean anything and everything that is even remotely related to any decision whatsoever in the business and that type of broad, vague definition does not help to understand what sorts of logic belong in the database and more importantly, what forms of logic do not belong in the database.

Yes, it is necessary to understand what business logic belongs in the database and what business logic doesn't. Understanding that doesn't however involve declaring that some things which clearly are business logic (for example the decision to so organise the sales team that each salesman can deal in only one product line, or equally the decision not to make the organisation like that, or the decision to leave the question open) are not business logic just because they have implications for constraints which belong in the database - changing the meaning of "business logic" just in order to allow the nonsense statement that the database must contain no business logic to appear to be true.


You are talking in circles here. Let's stop using the term "business logic" and instead define exactly what types of logic of which you speak because clearly our definition of the phrase is different. As far as I can tell to date, you have no bounds as to what defines and does not define "business logic".

If any and all logic is "business logic" then any form of logic makes sense to put in the database and that clearly is not the case.

Who has suggested that any and all logic is business logic other than yourself? And whence the enormous nonsequitur that if any and all logic were business than it would be appropriate to put all logic in the database - so far as I know, no-one has made the claim that all business logic should be in the database. Are you perhaps playing at putting up ludicrous strawmen to enable yourself to appear to logical in opposing a position which is nothing like those strawmen?


Actually, you did when you claimed that logic involving the data model, something that is clearly different than what most people define as "business logic" is just another form of the same. By lumping into the category of "business logic" things that most would not consider as such, you have left us to guess as to what *isn't* included in your definition of the phrase.

I could take your new suggestion as meaning that you want to assert that since all logic is business logic the database should contain no logic of any description, but I don't actually believe that you want to assert that. If you are claiming that I have asserted that you are claiming something which clearly is not true (as should be clear from reading any of my posts).


Not my assertion but rather my interpretation of what you might mean by the term. Again, without clearly defining what you mean (which is clearly diffrerent than what I mean) by the term "business logic" and given your remarks about table constraints being "business logic", you have left us to guess as to what you do *not* think is business logic.

To take an extreme case I am prepared to assert, for example, the logic used by Godel to demonstrating the consistency of the generalised continuum hypothesis with a commonly used axiomatisation of set theory and by Cohen to demonstrate its independence of those axioms could never under any circumstances be considered business logic.


Random and orthogonal.

Again, perhaps it would help to avoid the phrase "business logic" and instead think in terms of logic that relates to data correctness and logic that revolves around a business process. They both relate to a solution used for a business but only one generally belongs in the database.


The problem that the question whether data is correct is a business problem. It can only be ascertained or tested by examining business rules and can only be ensured by enforcing business rules.


And we're back to the kitchen sink definition of "business logic".

The business logic embodied in those particular business rules should be in the database, and I don't see any point at all in claiming that that logic is not business logic. Indeed if it were not business logic, why would it differ from one business to the next, as it clearly does?


Again, then let's not use the term. Exactly what types of logic should **not** be in the database?

Here's a simple rule for determing what business logic must be implemented in the database:
If a business rule can be enforced on the data using a combination of key constraints and/or domain constraints (in SQL as opposed to in relation algebra that amounts to primary key constraints, uniqueness constraints, foreign key constraints, not null constraints, qualifications of a column's datatype as to precision, scale, size, or collation, and check constraints) with reasonable modification (normalisation) of schemata to achieve this, then that should be done; the logic of the business rules enforced in this manner should therfor be implemented in the database.


In effect, what you are saying is that if you can express the logic using declarative features, then it should be in the database but this definition may be too narrow. What about logic implemented through triggers? What about database products that implement the ASSERTION keyword where you can in effect create check constraints that span tables? What about database products that do not implement check constraints but may implement triggers?

You seem to be stuck in the "how" the logic is implemented instead of the "where" it belongs or "why" it does or does not belong there. The database's purpose is to protect the correctness of the data itself but not necessarily a process that can easily change. Given that tables are sets of facts, it makes sense that the database is designed to ensure the correctness of those facts but not care about how those facts were gathered or what conventions were created to gather those facts.

It should also be implemented in the application logic, including the front end, since it is preferable to have things work without the application having to cope with constraint violations reported by the database, and for the user interface to avoid as far as reasonably possible presenting the end user with options that would violate the business rules (and the extremely rare cases when avoidance isn't reasonably possible giving the user instant feedback if there's a problem, rather than waiting for the database to signal a problem).


Agreed. However, by your use of the phrase "application logic", you have interestingly differentiated it from "business logic". Is it not the case that logic in the application is also "business logic" by your definition?

Application logic other than the above in the database? Well, I don't like exposing the database to ad-hoc queries, nor indeed exposing the schemata to the applications (I want maximal decoupling for modularity). So I prefer the applications to be forced to call stored procedures and have no permissions to read or write any table or view other than by calling the provided stored procedures.


Agreed but hard to enforce. Typically what blows up this approach is ad-hoc reports.

<snip>

But perhaps the fundamental problem with the "no business logic in the database" mantra is that it's used by many DBAs, DB developers, and DB designers as an excuse for making no attempt to understand the business, resulting in catastophically poor outcomes of development projects because without understanding the business they can't understand what data states are forbidden by the business logic or what data volumes are implied by technicalities of teh business and as a result can't design schemata that will provide data integrity and also can't estimate within several orders of magnitude what sort of storage is needed.


In the case of this thread at least, it comes down to vocabulary. To me, the logic used to ensure data correctness is different than the logic used to manage a business process is different than the logic used by one specific application that implements that logic and to lump multiple items into the bucket of "business logic" is equivalent to using the term "smurf" for any type of logic.

Databases last much longer than the applications used to initially write to them so their structure is critical to ensure lasting success. Each tier should be considered a fiefdom where no tier trusts data from the tier above it which means each tier re-validates the data given to it. When a date value is passed from the presentation tier, the middle-tier (sometimes called the business layer or business tier) re-validates that it is a date and the database again re-validates it. Each tier above the database acts like an emissary ensuring that the data that is sent to the next tier down will not be rejected but ultimately, they do not trust each other.

One guideline might be: if a rogue DBA can, without altering DRI or triggers but bypassing stored procedures, execute a query that alter data in such as way as to break other tiers of my application or create obviously wrong or inaccurate data, then the logic to protect that data and prevent that scenario belongs in the database at the most declarative level possible.

The database is there to protect the data. Most people do not call that "business logic" because the phrase quickly becomes meaningless. Most developers I have encountered understand the phrase "business logic" to mean logic not related to database data correctness nor related to the presentation tier.
Post #1222869
Posted Friday, December 16, 2011 11:21 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 8,296, Visits: 8,750
Thomas-282729 (12/15/2011)
L' Eomot Inversé (12/15/2011)
The problem that the question whether data is correct is a business problem. It can only be ascertained or tested by examining business rules and can only be ensured by enforcing business rules.


And we're back to the kitchen sink definition of "business logic".

I haven't a clue why you say that. I really haven't. If some logic is required by business rules, and would not be required if the business rules were different, it's business logic. If some logic is required regardless of what the business rules are, it's not business logic. If some logic is not required by any business rule but by a developer's sense of aesthetics it is not business logic. If some logic is required only by some prima donna's desire to achieve incredibly fast performance even though that fast performance isn't needed and even if the time taken to achieve it doesn't wreck the project the security and integrity flaws allowed just to get this unwanted performance will that's not business logic, it's what might be called anti-business logic. Is that clear enough? Do you see any kitchen sinks there?

In effect, what you are saying is that if you can express the logic using declarative features, then it should be in the database but this definition may be too narrow. What about logic implemented through triggers? What about database products that implement the ASSERTION keyword where you can in effect create check constraints that span tables? What about database products that do not implement check constraints but may implement triggers?

You seem to be stuck in the "how" the logic is implemented instead of the "where" it belongs or "why" it does or does not belong there.

What I said was that in the case of T-SQL the things that normally represent certain concepts of relational models are various things. As I expressed the idea in terms of relational concepts and gave a quick off-the-cuff description of what it covered in T-SQL it thought it would be clear that I don't in principal give a toss whether a domain constraint (a relational concept) is implemented in a database by a check constraint, or a type definition, a trigger, or an assertion, or something else because all I'm interested in is the underlying relational concept. The point is that the database has the job of ensuring that only some states of the data are permitted, and which states of the data are permitted is determined not by some magic theory unconnected to the business but by rules imposed by the business. These rules are normally (at the relational level) enforced by various constraints, and at the implementation level by a variety of mechanisms which, naturally, include constraints as well as other things.

The database's purpose is to protect the correctness of the data itself but not necessarily a process that can easily change. Given that tables are sets of facts, it makes sense that the database is designed to ensure the correctness of those facts but not care about how those facts were gathered or what conventions were created to gather those facts.

No, the database can't ensure the correctness of facts. For example it can't ensure that if John Smith's salary is 80000 Euros per annum the value held in the database is 80000.00, since someone may mistakenly enter it as 80000.05. All it can do is enforce some rules about what states of the database are valid; since individual salaries change from time to time the exact values of individual salaries are not something the database can police. On the other hand, if there is a business rule that all annual salaries lie between 18000 Euros and 500004 Euros per annum it can enforce that business rule as a domain constraint (because in relational algebra that's what that kind of business rule is).

It should also be implemented in the application logic, including the front end, since it is preferable to have things work without the application having to cope with constraint violations reported by the database, and for the user interface to avoid as far as reasonably possible presenting the end user with options that would violate the business rules (and the extremely rare cases when avoidance isn't reasonably possible giving the user instant feedback if there's a problem, rather than waiting for the database to signal a problem).


Agreed. However, by your use of the phrase "application logic", you have interestingly differentiated it from "business logic". Is it not the case that logic in the application is also "business logic" by your definition?

I used the word "logic" in the sense "programming"; shouldn't have done that, as it conflicts with my use of it in "business logic".

Application logic other than the above in the database? Well, I don't like exposing the database to ad-hoc queries, nor indeed exposing the schemata to the applications (I want maximal decoupling for modularity). So I prefer the applications to be forced to call stored procedures and have no permissions to read or write any table or view other than by calling the provided stored procedures.


Agreed but hard to enforce. Typically what blows up this approach is ad-hoc reports.

It seems to me that we actually actually agree on everything that matters, except terminology.
Actually I don't mind exposing the schema (with read-only access, of course) to people for the purpose of ad hoc reports, provided they are genuinely ad hoc and it is accepted that there's no guarantee that an ad-hoc report which worked yesterday will work tomorrow because there's no guarantee we won't change the schema to meet some business-critical objective. I've sold that idea successfully to top management a few times (it tends to be senior VPs and upwards who think they can insist on freezing the schema to make their pet report continue to work without anyone doing any work to maintain it; having the CEO and the COO on board tends to stop that kind of nonsense quite quickly).

But perhaps the fundamental problem with the "no business logic in the database" mantra is that it's used by many DBAs, DB developers, and DB designers as an excuse for making no attempt to understand the business, resulting in catastophically poor outcomes of development projects because without understanding the business they can't understand what data states are forbidden by the business logic or what data volumes are implied by technicalities of teh business and as a result can't design schemata that will provide data integrity and also can't estimate within several orders of magnitude what sort of storage is needed.


In the case of this thread at least, it comes down to vocabulary.

Maybe it's a culture difference causing a vocabulary difference. British ideas and American ideas are often very different. Or maybe it's just that you have been lucky and not encountered the sort of lazy idiot I described, so you are happy to exclude those business rules which determine the permitted states of the database from your definition of business logic.

To me, the logic used to ensure data correctness is different than the logic used to manage a business process is different than the logic used by one specific application that implements that logic and to lump multiple items into the bucket of "business logic" is equivalent to using the term "smurf" for any type of logic.

And to me, a refusal to recognise that a mechanism that enforces some business rules is necessarily an implementation of some business logic is contrary to the meanings of the words and flies in teh face of all commen sense.

Databases last much longer than the applications used to initially write to them so their structure is critical to ensure lasting success.

Actually they don't in a shop where requirements change continually (most shops are like that) - they change as rapidly as the applications, because what drives change is new business requirements and new business requirements often entail new data. A well designed database evolves easily, rather than having to be torn up and redone because of new requirements, and so does a well designed application. Modularity of design - including schema invisibility - makes it much easier to change the schema to support new things without breaking the old things, and to change the application to provide new features without damaging the old features. Of course the presentation layer may have to be torn up and thrown away to take advantage of new technology - going from ASP to ASP.NET, or from Flash to WPF is for the presentation layer the same sort of upheaval as would be going from IDMS-X to SQLServer for the database; but such things are almost as rare for application and presentation as they are for database.

Each tier should be considered a fiefdom where no tier trusts data from the tier above it which means each tier re-validates the data given to it. When a date value is passed from the presentation tier, the middle-tier (sometimes called the business layer or business tier) re-validates that it is a date and the database again re-validates it. Each tier above the database acts like an emissary ensuring that the data that is sent to the next tier down will not be rejected but ultimately, they do not trust each other.

One guideline might be: if a rogue DBA can, without altering DRI or triggers but bypassing stored procedures, execute a query that alter data in such as way as to break other tiers of my application or create obviously wrong or inaccurate data, then the logic to protect that data and prevent that scenario belongs in the database at the most declarative level possible.

If protection of the data state can be done without writing more DML than a trigger or a scalar function to be used only by a check constraint, then it should be done in the database. If it can be done without any DML, it probably should be.

The database is there to protect the data. Most people do not call that "business logic" because the phrase quickly becomes meaningless. Most developers I have encountered understand the phrase "business logic" to mean logic not related to database data correctness nor related to the presentation tier.

Well, you've encountered people who are different from some that I have met. And the database isn't just there to protect the data - that's just one of its functions, perhaps its primary function because it can't do any of the others reliably if it doesn't do that one. For example, it is also supposed to provide efficient access to the data (ie deliver the data access performance that the business needs), and enable the modification of existing data and the insertion of new data (just protecting it would be easy - no-one ever gets write access, and we keep lots of copies - but wouldn't be useful for most purposes).


Tom
Post #1223253
« Prev Topic | Next Topic »

Add to briefcase «««2829303132

Permissions Expand / Collapse