February 14, 2006 at 3:29 pm
I have worked as a developer as well as a DBA in numerous jobs and from my personal experience, I have to say that having the DRI in the DB is a great way to enforce these logic rules for the application thatg is being developed.
There have been many cases when developing on a DRI DB that I forget to code for a particular PK/FK, which means my app will throw up an error. I think this is a great benefit, as it ensures your developers always know what data is expected to be written to the DB - no exceptions.
February 14, 2006 at 6:32 pm
I am a VB6 /Java/C# programmer.
I used to work for a company that coded all of the RI in the business layer rather than at the data layer. It was an absolute nightmare to modify the database, as all of the constraints and reference were hidden in undocumented code.
I view the database component of an application as being a box that you put things in. The better constructed the box the easier it is to use. Basically the more time you spend getting the database right in the first place (including RI) the less time you have to go back to the database to confirm that your code won't break anything, as what ever object model you are using to connect to the database will return an error if you've tried to do something that you can't.
Cheers Jamie
PS If you think not having referential integrity is bad, what about a database that has no referential integrity and also every field is a varchar. Some of these fields contain numeric data which requires arithmetic manipulation.
February 15, 2006 at 1:51 am
I agree with Mark that there seems to be some confusion between DRI and cascading RI
February 15, 2006 at 3:11 am
Paul is correct, referential integrity can be used to enforce business rules: e.g defining the data flow in business processes. The single column primary key example though, enforces the integrity of the given business rule due to the unique constraint placed on the PK.
Within consultancy it may be true that business rules drive the logical model and it is a boon to be in this predicament but it doesn't always hold true for legacy systems. In the latter, most logical models remain well after the person who made the business rule has moved on or changed priorities. However, in principal I definitely agree, some of the most successfull projects that I have seen, have been those implemented from a concept or those completly overhauled.
Max
February 15, 2006 at 5:03 am
>> it doesn't always hold true for legacy systems. In the latter, most logical models remain well after the person who made the business rule has moved on or changed priorities. <<
Absolutely, but of course it may be (and in my personal experience has normally been) that the business rule has never changed, but was in fact badly implemented in the first place.
February 15, 2006 at 6:50 am
If we adopt Steve Jones "DBA CODE OF ETHICS", or not it boils down to this question.
WHO IS RESPONSIBLE FOR THE INTEGRITY OF THE DATA?
If it falls to the DBA, then why trust an application, as stated in previous postings. How better can a DBA protect the integrity of the data than by using referential integrity.
February 15, 2006 at 7:41 am
Define INTEGRITY
February 15, 2006 at 8:49 am
Here goes nothing!
I would defer to this definition, in addition to being in the DRI camp:
"The quality or condition of being whole or undivided; completeness."
Therefore child/sibling records MUST have parent records, not MAY have parent records. The only way to be 100% certain that this will happen is to create the physical relationships.
An application in theory may be 100% sure of maintaining this integrity, but not necessarily be 100% complete. One change to an application may inadvertently cause data integrity issues, then your left with stray ("divided") records and trying to figure how to "clean-up/fix" this data. Even though the application may have been through an intensive testing process how can someone be 100% certain that data integrity will always be maintained. The only way is physically through the database.
How can you be certain that your kid won't drive your car when you're on vacation? Plead with him and get his word, hide the car keys, siphon the gas from the tank, remove the battery, put the car up on blocks and remove the tires, or physically take the car with you on vacation.
steve
February 15, 2006 at 10:58 am
Data Integrity means that every field in every record in every table is accurate and complete.
February 15, 2006 at 11:12 am
Neither of you mention company policies. Is the data integrity compromised if, say, a price for an expensive product is zero?
February 15, 2006 at 12:04 pm
No it's not. Data integrity is about ensuring that the relationships between tables and records is maintained and enforced.
What you described would be a business rule, as the number in a particular column only has meaning at the Business Rule level. To the Database, any number is as valid as any other number. You can enforce such business rules in the database, though, using check constraints and triggers and the like
February 15, 2006 at 12:10 pm
I think we know the position that Dr. Codd, the "inventor" of DBRMS, would take on this issue ...
Rule 10 of Codd's Rules:
Integrity constraints specific to a particular RDB must be definable in the RDB sublanguage and storable in the catalog, not in the application programs.
Thank you Dr. C.!
February 15, 2006 at 12:11 pm
Sorry, DBRMS = RDBMS.
February 15, 2006 at 12:13 pm
The point I tried to make earlier, is that although you CAN enforce business rules with constaints and triggers, you SHOULDN'T. Those belong in an application's business logic layer, and not in the database. The database should DEFINITELY enforce INTEGRITY.
A DBA should control who can manipulate data, and using which applications, and should ensure auditing capabilities are in place. But if the application performs a function that violates a business rule, fix the application, not the database.
When you put business rule enforcement in a database, you are committing to using that database with a single application.
February 15, 2006 at 12:38 pm
I don't think the issue is whether or not a rule is a "business" rule.
When determining whether or not to enforce a business rule via the database, the issue is, as Dr. Codd states, whether a constraint under consideration is "specific to a particular RDB".
If a business rule doesn't apply to all applications that will sit on top of a particular database, then applying a constraint to the database to enforce such such a rule would not meet Dr. Codd's criteria in Rule 10.
However, when a business rule is specific to an RDB, then it is definitely appropriate to enforce such a rule in the database. For example, check constraints are frequently used to enforce "business" rules. This is entirely appropriate.
Viewing 15 posts - 31 through 45 (of 59 total)
You must be logged in to reply to this topic. Login to reply