| Slighty OT:Perspective on Referential Integrity & other DB integrity constraints [message #233355] |
Wed, 25 April 2007 16:11  |
tonmoy
Messages: 6
Registered: April 2007
|
Junior Member |
|
|
Fellow Database Developers,
I need your perspective as database developers on the topic of referential integrity and other database integrity constraints through a short survey (2 to 6 minutes). This is towards a paper I am developing on the subject.
I am a practicing Database Administrator/Database Developer (18+ years). Currently, I also double up as a graduate student in the newly developing science of “Information Quality”. (The world’s first graduate program was started Fall 2006 by the University of Arkansas at Little Rock in collaboration with the Massachusetts Institute of Technology).
Connecting my academic commitment to studies in Information/Data Quality with my experience as a database developer, I have decided to research on some questions that have intrigued me in the past. In my working experience, I have come across quite a number of relational databases deficient in database integrity constraint implementation such as referential integrity (RI). The questions that came to me were; what are the major reasons behind this lack of constraints? Database integrity constraints have prompted tremendous research and development in vendor laboratories and in academia. However, industry may have exhibited some reluctance and I am on the quest for the actual reasons from the field. The results of this survey should be of interest to practitioners and theorists in the areas of relational database development as well Information/Data quality.
The database constraints being referred to include referential integrity constraints, check constraints (Rules in the case of SQLServer/Sybase/Oracle) and triggers. To some, triggers may seem odd choice for business rules enforcement. However they offer a rich capability of rule enforcement besides it was also the only choice in certain RDBMs at one time. Questions have skip logic and your answers affect what further questions you are asked.
Confidentiality and anonymity is guaranteed in our survey. We do not ask for any personal information. While your participation is critical to the success of the research, it is also voluntary. None of the questions in the survey are mandatory. You may quit from the survey at any time by closing the browser window. A copy of the results will be available upon request. This survey is not sponsored by any commercial entity.
To start the survey, please follow the link http://www.ualr.edu/tkdasgupta/dbquality.htm
If you have any questions on the survey, do not hesitate to email me.
Thank you very much for your time and cooperation.
Tonmoy Dasgupta,
MSIQ Graduate Student,
IBM Certified DBA DB2 UDB V8.1 for z/OS
IBM Certified DBA DB2 UDB V8.1 for Linux, Unix and Windows
IBM Certified Solutions expert DB2 UDB V7.1
[Mod-edit: changed url. Removed trailing full stop]
[Updated on: Thu, 26 April 2007 01:11] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Slighty OT:Perspective on Referential Integrity & other DB integrity constraints [message #233846 is a reply to message #233355] |
Fri, 27 April 2007 14:02  |
tonmoy
Messages: 6
Registered: April 2007
|
Junior Member |
|
|
To all those who have participated in the survey “Perspectives on referential integrity & other database constraints” I express my sincere gratitude. And to those who have not, I once again beseech participation at http://www.ualr.edu/tkdasgupta/dbquality.htm
Every response is precious. It is a short survey and depending on your answers, could actually take as little as one minute, and at most six. Any database developer who has been involved in the implementation of a new relational database, qualifies – not just the DBA. I am seeking broad based perspectives, from practices in shops large and small. The topic of database integrity constraints is not of academic interest alone, otherwise we would not have quotes from the industry as listed in http://www.ualr.edu/tkdasgupta/Quote.htm
Once again I am required to repeat that we guarantee confidentiality and anonymity in our survey. We do not collect any personal information. While your participation is critical to the success of the research, it is also voluntary. None of the questions in the survey are mandatory. You may quit from the survey at any time by closing the browser window. This survey is not sponsored by any commercial entity.
Some of the comments left behind by respondents, have been very insightful. If you do wish to be identified with your comments, it is your voluntary choice, and the only place for leaving a communication id is within the comments box.
Please do not hesitate to contact me on any question you may have.
Thank you for your time.
Tonmoy Dasgupta,
MSIQ Graduate Student,
IBM Certified DBA DB2 UDB V8.1 for z/OS
IBM Certified DBA DB2 UDB V8.1 for Linux, Unix and Windows
IBM Certified Solutions expert DB2 UDB V7.1
|
|
|
|
| Re: Slighty OT:Perspective on Referential Integrity & other DB integrity constraints [message #233909 is a reply to message #233846] |
Sat, 28 April 2007 02:07 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
I may be mistaking, but I have never been in a discussion about how to handle referential integrity. Could it be that in the Oracle-world it is common use to let the DB handle it, because Oracle has had a good way to do it for a very long time now?
Could it be that this discussion stems from other databases? Or is caused by non-database developers that see the database simply as a place to store their data at night?
|
|
|
|
|
|
| Re: Slighty OT:Perspective on Referential Integrity & other DB integrity constraints [message #233987 is a reply to message #233909] |
Sat, 28 April 2007 13:40 |
tonmoy
Messages: 6
Registered: April 2007
|
Junior Member |
|
|
I want to make sure I understand...
Statement 1:
"I have never been in a discussion about how to handle referential integrity. Could it be that in the Oracle-world it is common use to let the DB handle it, because Oracle has had a good way to do it for a very long time now?" -Frank
Statement 2:
"The one application where they tried to do it at the application level, they ended up with a mess because not all parts of the application followed the same set of "rules"." - Anacedent
Does (Statement 1) mean that RI is almost always implemented in Oracle databases? Do you not have cases where the project management (or whoever) chose not to, or was unable to implement RI? You surely have to do more work to implement RI than in database implementations that do not.
My experiences is mainly with IBM database products and RI at the database level is always strongly advocated. And IBM has implemented RI enablement robustly since the early days of the RDBMS. However some folks do not implement RI for various reasons. I am on the hunt for those reasons and I thought that the problems should apply universally across all RDBMSs. One of the bigger concerns I had come across is performace overhead with RI.
"Statement 2" tallies as a bad practice with Roger Miller's quote in http://www.ualr.edu/tkdasgupta/Quote.htm So it is not a Oracle vs other rdbms issue I think...
Tonmoy
|
|
|
|
| Re: Slighty OT:Perspective on Referential Integrity & other DB integrity constraints [message #233990 is a reply to message #233987] |
Sat, 28 April 2007 14:02 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
There is NO performance overhead with RI at database-level in Oracle. Period.
About your other remark, about management wanting referential integrity be done in the application instead of at rb-level, I have only one thing to say: let management deal with management, then I will deal with what I am good at.
Seriously, any project where either management or analysts try to do my job, I quit. If they know so well, let them do it.
|
|
|
|
| Re: Slighty OT:Perspective on Referential Integrity & other DB integrity constraints [message #234244 is a reply to message #233990] |
Mon, 30 April 2007 10:18 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
Frank's last statement is so true.
One job I worked at said we have to remove all database triggers from the database because it is too proprietary and all business rules must be put into the java front end because it is "open source." All Oracle Forms were to be rewritten in Java or Javascript. Explaining to them that this was was, to put it kindly, asinine, fell on deaf ears.
I started looking for a new job that same day and got out about three months later.
Two years later they still haven't been able to produce a front-end to replace an Oracle Form and they are still pulling their hair out of their heads trying to find out why they don't have referential integrity anymore. All I can do it laugh.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
