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

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.

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

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

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

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,

Thank you very much for the continued advice.
Gotchya. Understood.
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.
Nah. I'll just see how this all works out.

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

Regards,

dog_dude

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.

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