Home » Other » General » Slighty OT:Perspective on Referential Integrity & other DB integrity constraints
Slighty OT:Perspective on Referential Integrity & other DB integrity constraints [message #233355] Wed, 25 April 2007 16:11 Go to next message
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 #233402 is a reply to message #233355] Thu, 26 April 2007 01:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
404 Page not found

[Edit:Found the cause; edited your link]

[Updated on: Thu, 26 April 2007 01:11]

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 #233966 is a reply to message #233355] Sat, 28 April 2007 09:06 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>Could it be that this discussion stems from other databases?
I suspect this is an "issue" for some software vendors who wish to have their application be database independent.
Therefore they HAVE to code to the lowest capable RDBMS system.
Alterntively I have heard of similar discussions WRT data integrity being enforced at "business rules" level as opposed to RDBMS level.
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".
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
joy_division
Messages: 4640
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.
Re: Slighty OT:Perspective on Referential Integrity & other DB integrity constraints [message #234283 is a reply to message #233355] Mon, 30 April 2007 13:21 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
Joy,
The upside is that they can now easily move onto any other RDBMS & continue to have the same problems they have today.
Re: Slighty OT:Perspective on Referential Integrity & other DB integrity constraints [message #234291 is a reply to message #234283] Mon, 30 April 2007 13:42 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Hahaha, that was basically their reason.
Re: Slighty OT:Perspective on Referential Integrity & other DB integrity constraints [message #234295 is a reply to message #234291] Mon, 30 April 2007 14:14 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Of course there's overhead in enforcing RI. No processing comes for free. Does the overhead usually outweigh the benefit - No.
Re: Slighty OT:Perspective on Referential Integrity & other DB integrity constraints [message #234316 is a reply to message #234295] Mon, 30 April 2007 16:02 Go to previous messageGo to next message
tonmoy
Messages: 6
Registered: April 2007
Junior Member
Ah, thank you...I was wondering what magic Oracle might have come up with that has eluded all others. Oracle may be more efficient or the earliest to raise the bar, but zero overhead for RI? I thought that it was some communication issue where I was not understanding the point or I had miscommunicated.

Re: Slighty OT:Perspective on Referential Integrity & other DB integrity constraints [message #235906 is a reply to message #234316] Tue, 08 May 2007 06:44 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
Of course constraints, rules whatever they are called in any DB takes some overhead, but the overhead is the least if we compare the RI done in DB or application. Also this overhead is justified, just like backups are pure overhead but justified as well.
Without RI and rules we get data waste instead of data base as I've written here
http://www.gplivna.eu/papers/data_waste_or_data_base.htm

Gints Plivna
http://www.gplivna.eu
Re: Slighty OT:Perspective on Referential Integrity & other DB integrity constraints [message #235977 is a reply to message #235906] Tue, 08 May 2007 10:08 Go to previous messageGo to next message
tonmoy
Messages: 6
Registered: April 2007
Junior Member
Thank you Gints for sharing your link on "data-waste"...your article is interesting. I see that you have a link to Tom Kyles blog...I tried to locate the 10 worst practices and could not locate it. Do you know if he made his presentation available?
Re: Slighty OT:Perspective on Referential Integrity & other DB integrity constraints [message #236001 is a reply to message #235977] Tue, 08 May 2007 11:59 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
tonmoy wrote on Tue, 08 May 2007 18:08
I see that you have a link to Tom Kyles blog...I tried to locate the 10 worst practices and could not locate it. Do you know if he made his presentation available?


Unfortunately I don't know.
However you can ask him in his blog or write a mail or whatever...
And then share the info with us Smile

Gints Plivna
http://www.gplivna.eu
Re: Slighty OT:Perspective on Referential Integrity & other DB integrity constraints [message #236109 is a reply to message #236001] Wed, 09 May 2007 01:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It's in the Files-section of AskTom:
Here
Re: Slighty OT:Perspective on Referential Integrity & other DB integrity constraints [message #236183 is a reply to message #236109] Wed, 09 May 2007 04:31 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
Thank you!
Re: Slighty OT:Perspective on Referential Integrity & other DB integrity constraints [message #236400 is a reply to message #236109] Wed, 09 May 2007 20:54 Go to previous messageGo to next message
tonmoy
Messages: 6
Registered: April 2007
Junior Member
Thanks Frank. That is for keeps. I am not a Oracle person so I am asking this question. Am I correct in guessing that Tom Kyte is some sort of Oracle celebrity... and if so, what is his speciality...area that he is best known for?

Tonmoy.

[Updated on: Wed, 09 May 2007 20:55]

Report message to a moderator

Re: Slighty OT:Perspective on Referential Integrity & other DB integrity constraints [message #236526 is a reply to message #236400] Thu, 10 May 2007 03:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Yes, Thomas Kyte is a well respected member of the Oracle community. He is a VP at Oracle Corporation, and specialises in database-related stuff (so, no Developer/Designer etc).
He is well known for his AskTom site (www.asktom.com)
Re: Slighty OT:Perspective on Referential Integrity & other DB integrity constraints [message #236745 is a reply to message #233355] Thu, 10 May 2007 16:55 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
I always use http://asktom.oracle.com instead of URL below
>He is well known for his AskTom site (www.asktom.com)

[Updated on: Thu, 10 May 2007 16:56] by Moderator

Report message to a moderator

Re: Slighty OT:Perspective on Referential Integrity & other DB integrity constraints [message #236820 is a reply to message #236745] Fri, 11 May 2007 01:38 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
oops.
That's the penalty for using bookmarks, I guess...

[Updated on: Fri, 11 May 2007 01:39]

Report message to a moderator

Previous Topic: ts$
Next Topic: Who can offer a metalink account?My account is disabled by previous company
Goto Forum:
  


Current Time: Sat Dec 03 20:41:09 CST 2016

Total time taken to generate the page: 0.04712 seconds