DRI or No DRI?
-
If you enforce DRI through the DBMS, you know it will be enforced for any relevant DML operation, no matter whether it is done via the application, or from an Access front-end or through Query Analyzer or OSQL etc. There can be a slight performance hit, but any good DBA can minimise that.
If you enforce the rules via an application, you have to ensure that data updates are only EVER done through the application, and you need to ensure the rules are consistent for every application module, code changes etc.
As soon as data is allowed to be modified outside of the controlled application, you lose your data integrity. Any user with insert or update access to the database (that's a separate security issue!) can use any ODBC tool to connect to the database and run DML unrestricted by application-enforced rules.
I've had several "fixes" sent to me by developers that have failed in execution as the DRI caused an error because the developers "forgot" about the data relationships. If it had been application managed, there would be no error to stop the code, and there would have been either a recovery or another "fix" from the developers to sort it out.
On the other hand, for a Data Warehouse it is often acceptable not to use DRI. There can be several reasons for this, e.g. because the data is from a "trusted source" and doesn't need to be validated again.
-
If you are enforcing business rules outside of the DBMS, then you have poorly designed your database, or don't fully understand what a database is.
Your conceptual model (or business model) should define business rules, and you should transfer these to your database design. If you don't, then it's incomplete.
Practically, as far as I can see, the decision is a no-brainer for reasons already given by others.
-
DBMS ?=? ORDMS ?=? ODMS
-
Don't go there!!!!
-
Many years, and several careers ago, I had the great good fortune of studying Linguistics and Language Teaching under a brilliant man. One of the lessons I learned from Karl has served me well in many situations, not just in teaching, and I think the concept applies here as well. Karl liked to say that there three ways in which one can define themselves in their careers. Briefly, they are:
Day Laborer. These are the people who clock in, ask for a list of tasks for the day, plug away at the list, and clock out again. These folks see no reason to be concerned about the outcome of their efforts, so long as they put in the required number of hours each day.
Artists. These are the people who consider their own creativity--and more importantly, the opportunity to express it in their work--to be the most important part of any situation. They refuse to be held accountable for outcomes because, after all, they are constantly creating and re-creating as they go. Standards and work rules, project goals, "best practices", none of those is as important as their own unique ability to come up with elegant, if impractical, solutions. Because each new assignment is seen as an opportunity to express creativity, they seldom learn from their own experience, much less that of others.
Craftsmen. These are the people who take their craft seriously, who study hard to master it, glean best practices from others, learn and apply standards and principles, and measure their success by how well the outcomes of their efforts measure up to the goals set for them. Getting the job done is only half the battle; doing it WELL is the criterion by which they meaure success.
It may be a pain in the b*tt to understand and apply things like Normalization and RI to ones database. It may interfere with the creative process that goes into designing and building an application, but a craftsman, IMO, knows that that is the right way to go about it and would be embarrassed not to at least make the attempt.
That's my 2 cents.
George
-
I'm not communicating effectively and I apologize. Again, I think many are not making a distinction between enforcing data integrity and enforcing business rules related to process.
No sh** you better be enforcing RI at the database. And you best constrain data types to values found in the objects the data represents.
There is a difference between data sitting static in a database, and representing a snapshot of your business accurately and WITH INTEGRITY. The database should never allow the data to lose INTEGRITY. It should not represent a snopshot of your business that CANNOT OCCUR in reality.
Is the concept of n-Tier design lost on some you? Have you dismissed it? Or am I not making clear WHICH part of application design doesn't belong in the database. Clearly, much does.
I'm obviously passionate about this because I have had to deliver functionality that was almost impossible to deliver because the data base was a rats nest of triggers that, when disabled, broke one application, and while enabled, made delivering the required functionality impossible.
If I have to redesign two other applications to provide the requested service to management, the design of the two other applications and the database is flawed. Period.
-
The topic of this discussion is DRI. By definition, DRI is implemented via foreign key contraints. Enforcing referential integrity with triggers is not DRI.
The business layer vs. trigger debate is one discussion. The business layer vs. DRI debate is another.
When the alternatives are either to enforce referential integrity in the business layer of an application or in an underlying database via DRI (foreign key constraints), then DRI trumps the business layer every time.
-
I guess we moved beyond foreign key constraints because limiting the discussion to DRI or even RI through triggers isn't much of a debate. If you participate in these forums, your head isn't in the sand, and you enforce foreign keys in the database. I think I'll take a nap now.
- Jeff -
-
Alright...moving beyond foreign key constraints.
When the alternatives are either to enforce data integrity in the business layer of an application or in an underlying database via any kind of constraint (primary key, foreign key, check, unique, not null), then constraints trump the business layer every time.
-
Still perfect, Chris. A no brainer.
Now developers and DBAs simply need to carefully recognize the definition of data integrity. Which is still out of range of this thread, you're right.
This is where some will argue that if a policy was violated (let's say I want to ship a vaccine, leaving my FEMA reserve below policy limits--I'm supposed to wait until manufacturing catches up) the integrity of the data is compromised. But that's not data integrity. Yet I find many databases where a trigger won't allow an update because the inventory would drop below acceptable limits. That's an important control, but it doesn't belong in a trigger.
This is simply a discussion that needs to occur, because some developers and DBAs alike, will blur those lines of distinction.
-
George, you are right on about the craftsman. Too many people are no longer willing to work to be one.
-
"A no brainer"? "Limiting the discussion to DRI...isn't much of a debate"? "If you participate in these forums...you enforce foreign keys in the database"?
Jeffrey, did you read the title of this discussion? Did you read the editorial and/or the PK/FK discussion that inspired this line?
I apologize to have bored anyone with comments regarding "DRI or No DRI?" or something remotely related. Apparently, these comments made Jeffrey so sleepy that he had to take a nap. Sorry friend. Clearly the topic is beneath you.
This is probably over my head, but what is the definition of data integrity? I'm just guessing, but could it be data that's accurate? If you have database field called VaccineInventoryCount_WarehouseB, then I'm guessing that it should accurately reflect the the actual vaccine inventory count in warehouse B (violated policy or not).
That's all I got.
-
Idealism vs. Realism, I'd like to see that on "Celebrity Deathmatch"!
In response to Chris's request for information on data integrity: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_3bsp.asp
Max
-
You know, I'm getting real tired of the assumption by DBAs that developers are lazy and that's the end-all of the reason that we write software you don't like. You know, it's probably more like DBAs are wannabee developers that couldn't cut the mustard so they live in their little world of stored procedures since they can't write code.
-
To argue “DRI or No DRI” is to argue using a false dilemma. Sometimes the decision is not so black-and-white. Personally, I use DRI unless there is a *very* good reason to do otherwise. However, I *have* encountered situations where DRI simply could not handle the situation I was trying to model without cluttering the database and thus for those select set of tables I did not use DRI.
In relation to the business rules sub-thread, it is important to understand the role of the database in the entire system of applications built on that database. The fiefdom analogy seems to work best in understanding this situation. Think of each layer in an n-layer system as a fiefdom. No fiefdom trusts data from any other fiefdom. The next closest layer acts as an emissary to help facilitate information getting accepted by the next layer down in the chain but that does not preclude the accepting layer from re-checking the data. So, if we have a business rule that says something like “balance cannot be less than zero”, that rule may be checked at the presentation, business *and* data layers. DRI is one of the database’s primary mechanisms for performing those checks.
The data layer is only one location for business rules but it is not the only place for rules. IMO, the ideal situation is that all rules are espoused in the business layer even if they are also enforced in one or more other layers. This makes it easier to document “all” the rules as they should all exist in the business layer.
Viewing 15 posts - 46 through 59 (of 59 total)
You must be logged in to reply to this topic. Login to reply