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.

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.