Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2006
    Posts
    5

    Referential Integrity: Always?

    Hello, first post on this forum.

    I'm having a difficult time trying to find Admins' views on why you shouldn't use RI. I myself always like to use it, but I'm curious as to why some pros would not. Got opinions on this matter?

    Thanks for any advice or suggestions!

    dog_dude

  2. #2
    Join Date
    Feb 2005
    Location
    Colorado Springs
    Posts
    222
    I'm with you - RI is essential to maintaining the integrity of your database. Now, some database programs have it built in and others may need to have it coded with functions - I think earlier versions of MySQL would be an example. Also, some databases confuse referential integrity with data models - Paradox would be an example. In Paradox, one didn't want to use the built in RI because you couldn't then use alternate data models easily. The only reasons I can think of not to use RI are 1) possibly speed, 2) the manner in which it is implemented in a particular RDBMS system, and 3) a simple data model that can be represented without it.

  3. #3
    Join Date
    Mar 2006
    Posts
    5
    Thanks for the advice, ByteRyder52!

    I've been told by management that our company, with our enormous database, should not use referential integrity because it can 'cause more headaches' than not having it. I, of course, disagree. I mean, sure, there's more to keep up with; but isn't that how a modern database is supposed to work (better yet, isn't that a DB admin's job???)???.

    Oh, well, I just couldn't see the reason not to enforce RI on our DB tables; management sees it as a hindrance.

    Thanks again!

    dog_dude

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dog_dude
    I've been told by management that our company, with our enormous database, should not use referential integrity because it can 'cause more headaches' than not having it.
    this is a most interesting statement and it surely must be examined more closely

    i'm afraid i must agree with your management

    "referential integrity can cause headaches" is a great example of a statement which is egregiously out of context

    in this case, the context is the cost of the project to implement RI into an enormous database which currently works fine without RI

    and the database surely must be working, a fair assumption given that the business is obviously a going concern, since you are employed there

    so i totally agree with your management

    and you know what is in fairly immediate danger of being seen as a hindrance, don't you

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2006
    Posts
    5
    Interesting. That's why I asked! Thanks for the input, r937.

    Of course, the question begs to be asked: Do we know that all facets of the database is working properly?

    I simply think that we rely too much on programming to protect against data duplication, data loss, etc --rather than enforce rules using tools built into the RDBMS-- thus unnecessarily involving the inconsistent and sloppy human element.

    I do understand what you're saying about management deciding that the costs to implement RI outweigh the costs associated with not having RI. As a member of development, I guess I tend to think more in the long-term (years ahead, rather than quarterly, semi-annually, etc.).

    Thanks again for the informative input!

    dog_dude

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i understand (trust me, i do, i've worked for the man, and i've also been the man)

    you think in the long term, but you must pay for the effort up front

    oh, if only projects could actually show ROI before they were started!

    in other words, if we could actually start the money coming in to the company from converting to RI, before we actually had to invest a few man-centuries to converting to RI...

    i'll give you a little hint: you are never going to retrofit RI into your existing enormous database

    what you can do is build your next couple of new tables with RI -- just be ready to defend yourself against the inevitable onslaught of angry developers

    sometimes, showing them how they have less work to do than they did before will get them to calm down

    and hey, if you cannot show the benefits to developers, ...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2006
    Posts
    5
    rudy,

    Thank you very much for the continued advice.
    oh, if only projects could actually show ROI before they were started!

    in other words, if we could actually start the money coming in to the company from converting to RI, before we actually had to invest a few man-centuries to converting to RI...
    Gotchya. Understood.
    i'll give you a little hint: you are never going to retrofit RI into your existing enormous database
    That's exactly what I needed to hear from a pro. It sucks, but if that's the way it is, I'll accept it.
    what you can do is build your next couple of new tables with RI -- just be ready to defend yourself against the inevitable onslaught of angry developers
    Nah. I'll just see how this all works out.

    Thanks again, rudy! Your advice is just what I needed.

    Regards,

    dog_dude

  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    In the perfect world, systems do benefit from using RI. It allows you to keep your clients "lighter," and to maintain your business rules in the database, where (IMO) they belong, rather than at the client.

    However, legacy systems, or those systems upgraded from legacy systems often use non-ideal approaches, as the cost to completely rewrite a working app may not have been justifiable when the upgrade occurred.

    And, in order to maintain consistency in the product, it may be a business decision to maintain a common approach with any new features that are added to the existing system. Otherwise, it becomes difficult to know, especially for new users, when the legacy approach is used, and when the RI approach is used.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  9. #9
    Join Date
    Mar 2006
    Posts
    5
    Gotchya, loquin. Thanks a lot for the words of wisdom! I've built a lot of DBs, but have never experienced a legacy DB of such enormity. I was hoping that we would take the RI route; but now I'm learning of reasons why not to in this particular case. Your alls' advice is invaluable to me, and I greatly appreciate it.

    Regards,

    dog_dude

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •