A new Quote of the Week was posted on the QUOTES page. Consider it in the context of my posts on database vs. application enforced integrity.
2.
A new To Laugh or Cry? item was posted on the LAUGH/CRY? page.
Do you like this post? Please link back to this article by copying one of the codes below.
URL: HTML link code: BB (forum) link code:
The 12/12/12 QotW was a corker alright, though I see no reason not to enforce data integrity in the application too--as long as the constraints are discovered dynamically from the database.
ReplyDeleteThe 12/12/12 LoC did constain one encouraging nugget. The author uses the term "data store" rather than database. I applaud that. The term database has become so debased I can't easily bring myself to use it anymore. Perhaps if more people were to pick up the term data store or something else, the word database can be rehabilitated.
All the reasons for which integrity should not be enforced by applications kick in whether it's redundant with the DBMS enforcement or not.
ReplyDeleteThe logical-physical confusion is so widespread and entrenched that it's practically impossible to eliminate it. Practioners are completely unaware of it and don't get it even if you point it out to them. That's because nowhere in their education or career they are exposed to the distinction.
You write "All the reasons for which integrity should not be enforced by applications kick in **whether it's redundant with the DBMS enforcement or not**." (Emphasis mine.)
ReplyDeleteCan you direct me/us to some reading that explains why integrity should never be be redundantly enforced in the application? I have not seen such a strong claim before. I also admit that I don't see any logical/physical confusion. I wonder what I'm not understanding.
The reason integrity enforcement is relegated to the DBMS is to avoid the drawbacks of application enforcement: development and maintenance burdens, complexity (handling concurrency and optimization), error proneness and poor reliability across developers and apps and so on.
DeleteIf the DBMS does enforce integrity, why would you want to also keep the very problems that that is intended to prevent? Is that productive or cost-effective? Not to mention conflicts between the 2 levels of constraints?
To get a sense of the problematics involved wait until I publish a forthcoming 2-3 parter on the subject that I am working on.
I now see your objection is to "hand-crafted" integrity checks in the application. I completely agree that should never be done.
DeleteHowever, when I wrote "as long as the constraints are discovered dynamically from the database" I intended it to be understood that the API (not the programmer) discovers the constraints in the database and applies them in the application automatically, with no programmer intervention at all. The API must of course ensure it is working with fully up-to-date constraints at all times, but that is entirely feasible.
I envision something very like the way the APIs already enforce type matching, but extended to domain checks, bounds checks and anything else that can usefully be done to reject inconsistent input at the earliest moment and before it is ever sent to the DBMS.
Data quality is paramount, but the user experience is a close second. I don't see why we can't have both at very little cost.
I suspect that there are performance implications. We already have the industry trading off integrity for performance; and as you shall see from my forthcoming article, simultaneous efficient and correct handling of concurrency in constraint formulation is pretty hairy stuff.
DeleteWell, the normal use of the term "application-enforced integrity" refers to coded in the application. I saw your different use of the term, but I am not as certain as you are as to the feasibility and implications in practice if not in principle, knowing how the industry operates.
ReplyDelete