DRI or No DRI?
-
I have worked in several envrironments and always start at the database level and ensure all rules are applied here. only at this point would I move onto any application design.
I am more and more coming across systems that have been written with a database backend such as SQL Server or Oracle that are poorly written with all reliance for business rules, integrity etc being done at the application level. This is a real frustration for me especially as I spend a lot of time in the database side of these systems creating reports and often bolt on interfaces.
-
You need PKs and FKs in a proper database design, so why not implement them in the DBMS? What do you gain that outweighs what you are losing? The idea is that the DBMS ensures correctness by its design. If you only implement part of the design in the DBMS (or, worse, don't properly design the database), then all you're doing is using the DBMS as a SQL parser.
I've lost count of the times when an application bug has been highlighted by a database (RI) error, that would otherwise have potentially gone unnoticed for some time. I'd rather stick pins in my eyes than not use DRI!
Picture this too: I have a product catalogue with a central product database, PK being product code, and several tables with FKs to product code. I want to delete a product... with DRI I just issue a delete statement to the Products table and it cascades out. Without DRI I'd need to issue a delete statement for each table. If I add a table, more code needs changing.
There will always be bugs in code. At least if you use DRI in your DBMS then you have a solid foundation for your data, and thus your application.
I'd also be willing to bet money that most that do not use DRI have never had to clear up the consequences...
-
Please, please, make time to explain why you don't see the value of RI in enforcing data integrity? And even more interesting, why you consider it to be "bad enough" to have to encode business/data logic in SPs and UDFs? When and where do you think enforcing business rules should be done? Please say that you do think it should be done somewhere.
I've never run across this particular philosophy among the developers I know. I am very curious about the thinking that goes into it.
George
-
In David's defense business logic shouldn't be enforced by DRI. SQL Server provides constraints and triggers for this purpose.
Business logic and referential integrity should also not be confused. They do, however, provide powerful possibilities (excuse the alliteration) when combined.
Max
-
Max makes a good point. Many n-tier developers try to drive too much into the middle "business layer". But I would argue enforcement of a data model is not business logic. When modelling data, the principles behind that model should be enforced.
Should I be able to transfer money out of an account that will bring the balance below zero? That is business logic and belongs in the business layer. The database is responsible for securing, auditing, and maintaining data integrity. And it should be designed to do it well.
- Jeff -
-
First of all I do take offense to your assertion that developers are lazy, they might be ignorant at times about proper db design, but to lump all as lazy is a travesty. I hope you realize your own ignorance on this matter and get off your high horse of dbas being gods.
I personally use triggers to enforce referential intergrity at the database level since they are more flexible than using DRI when it comes to changing database schemas and enforcing cascading udates and deletes. That's my personal preference and unless someone can convince me that's a bad practice I will continue to use it in most cases. If I were developing a small app with fairly stable schema then I would pursue DRIs. I think both approaches enforce data integrity at the database level.
Thank you,
Jay.
-
Having worked in several different database environments it first puzzled me why the larger database manaufacturers opted not to use DRI in there ERP systems (Oracle 11i is a good example). After having to modify hundreds of programs, reports and forms I slowly began to understand that DRI implementation just is not the best fit for a highly customized ERP system which changes dynamically at the push of a button.
John
-
If I am reading this correctly I think you are describing an application which can be customized to be an ERP system (e.g. Remedy ACS which I have used for a cusomizable help desk solution) rather than a specific non customizable ERP system for which the schema is known in advance. If I am correct then I would expect that the underlying system which is used to describe the custom ERP system would itself use DRI in its internal schema.
-
I worked for a company that made customized laboratory information management systems. The lifecyle for these applications was approximately 18 to 36 months, and the applications sold at a minimum of $150,000. Clients included municipal water companies, medical testing laboratories, etc.
As you might imagine, data integrity was essential. Despite this fact, most of the applications did not use DRI (depending on the developer). The programming manager once told me that "relationships are textbook stuff". As you might suspect, after such applications were deployed, they were extremely buggy and consistently reported bad data. Not surprisingly, the company was sued by two clients and went bankrupt.
Another prior employer made billing software for health care providers (primarily hospitals). The programming manager there told me "I hate relationships; they slow the applications down." When the contracts for their cornerstone application came up for renewal, nearly all customers passed. The company laid-off 1/2 of their IT department and is now in the process of redeveloping the application from scatch.
I don't know if it was lack of knowledge or laziness that poisoned these guys' attitudes about applying fundamentals of database development, but skip DRI at your own risk.
-
To Chris Woodcock,
Oracle 11i (we are on version 11.5.7) is a major ERP system used by thousands of companies for various purposes - ours is used to manufacture very large pumps. The underlying database (Oracle 9i in this instance) relies on programmatic referential integrity instead of PK/FK DRI through the application layer. You can theoretically delete a master entry and orphan the detail (this very thing has been the cause of many "Family patches" in Oracle). The biggest problem with major-league systems is that most companies require some customization to fit their needs and this results in many bug fixes being applied because DRI is not implemented.
-
The use of DRI in the database can be a good thing but I don't know if I'd ever fully implement it there. I look at it from a pratical stand point. Where do I want my code. If I have a backend application then putting logic in the database is ok but if I have a GUI application then I will end up with a lot of integrity logic in my GUI which I really don't want to repeat in the database. When I make changes I want to only change one place. That is either in the GUI or in the Database. Since I usually don't have a choice about the GUI then I tend to put a minimal amount in the database. Usually just forieng keys.
I do agree that you should have some DRI but it should be in moderation. You need to remember what you are building. A database or an application?
-
Leaving your data integrity constraints in the application code assumes that your developer is also a good DBA. Sure, there are a few of them (us) who work on both sides of this fence, but I wouldn't trust the integrity of my database to an app. Build in the necessary constraints (even if it's only PKs and FKs), and let the database do what it does best.
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices -
Your business model (including business rules) drives your logical model (i.e. the database implementation) and thus the "principles behind that model" should be one and the same.
In your zero-balance example, if an account was defined in your business model having property "balance" that "could be a decimal value with 2 decimal places and can never be below zero", then that is part of your model and should be enforced by the DBMS.
If, however, business processes said that if an transaction will take an account below zero, then this must go to a certain bank employee (or certain type of employee) to be approved or rejected, then this is a process and would of course be handled by the application (but "balance" would be defined as being able to have a value below zero, so the DBMS is still handling the business rules, just not the processes).
However, as Max pointed out, this is not referential integrity. He is technically wrong to say DRI should not enforce business rules (it should enforce some: e.g. consider: "each account is uniquely identifiable by its account number"; primary keys are business rules), but would be correct in saying that DRI does not enforce all business rules. However, it is wrong to derive from this that business rules should be enforced in a separate "layer", implying this is outside the DBMS. Rather, they are handled by other constraints (such as CHECK constraints in SQL Server).
-
Paul illustrates how carefully you have to consider enforcing data integrity versus business rules.
I think an account balance is a good example of a data element that can go below zero. Application A may have business rules that say it may not, while Application B may allow it. If it's enforced at the database, Application B could not function without breaking Application A.
Consider an attribute "Spots on a Dog". A dog could never have negative spots. If it did, the data would be corrupt. It's a physical impossibility and should be enforced as such. This is different than an account balance. (I thought spots were clearer than "current inventory" or some mundane data point).
Data becomes corrupt when it no longer has meaning in the world it models. Anything else is a business rule. Isn't it?
-
Not quite but nearly... business rules that form your business model, which forms your logical model, are enforced by the DBMS.
Such things as "when someone does this, or tries to do this, this should happen" are specifications for the application. They are not business rules as such, in the way we're talking about them in terms of modeling.
>> I think an account balance is a good example of a data element that can go below zero. Application A may have business rules that say it may not, while Application B may allow it. If it's enforced at the database, Application B could not function without breaking Application A. <<
So, taking this a little further, what would in fact be the case is that you'd define central business rules that would define "balance".
If one application then wanted to further restrict this by not allowing it to do something, then that is part of the specification of that application. Now, that might be a rule in the business, but it's a specification, not a business rule. It's not part of your model.
Your business rule enforced by the DBMS would be along the lines of "a decimal value with 2 decimal places, positive or negative". (Of course the precise definition would depend on the business.)
Your specification that Application A fulfills might then be "if a customer accessing his/her account via Internet Banking tries to transfer more than their available balance out, disallow this". But because you allow balances below zero, this isn't a business rule. This could be demonstrated by, say, bringing overdrafts into it. The application would then be using the balance, the overdraft limit and user inputs to follow a set specification and write data back (or respond otherwise accordingly).
So you need to differentiate between what are business rules and what are specifications for an application.
Viewing 15 posts - 16 through 30 (of 59 total)
You must be logged in to reply to this topic. Login to reply