Re: discussion on RI

I view the use of RI in the database and the use of code in the application as serving two different purposes.

1. RI (and triggers and any other DB-side code) is used to protect the validity/consistency of the data. It is the primary tool used to implement the most important purpose of the DB. That is, ensuring that you always have good data. (of course it can't help if someone spells a name wrong, etc.)
2. Client-side code is used to provide a better user experience. It allows for quick responses to errors that are made. The fact that it may also contribute to clean data is nice. But the DB, especially declarative RI, is the primary defense.

Given that, it is vital that the RI be included from day one. The RI informs the developer that they haven't caught an error. It allows them to include code to avoid delivering cryptic ORA-xxxx errors to the user. But if they forget to code for an error, then the ORA-xxxx will appear anyway.

I have an example of this. We wrote a large web application. When doing training, we were unable to insert some data. It turned out that we had not correctly coded client-side checks for some condition. However, the RI in the DB prevented the invalid/inconsistent data from being inserted. Everyone was really unhappy, but I was ecstatic. The RI had done exactly what I wanted by providing a check on the code.

If you get the RI right, you can survive mistakes in the code. If you do not get the RI right, you are at the mercy of whatever errors (or more importantly, omissions) are made by developers.

In addition, getting rid of bad data is a lot more difficult than building in the protection from day one.

> I will only say that for this statement I disagree. <SNIP> allow application logic/flow to be fully developed inside the
> database, therefore they prefer to keep it in one location where it can be
> managed: the code (Java, ASP, C++). <SNIP>
>
> Having the RI in the database makes much more sense that in the code where the developers have to keep track of it and it can easily get changed when passing from developer to developer d revisions are made to some code modules and not others....... It
 is almost always better to control as much of that as you can with the database...If changes are needed you only have to make it in one place and no go looking on a code finding hunt...
>
> My 02 cents
>
> Mike
>
> >>> Kate Monro <KMonro_at_extend.com> 05/17 12:45 PM >>>
> There are some software architects where I work who advocate (for short
> timeline internet app databases) not using relational integrity. We use
> column level constraints (not null, default, PK, FK relations) but no RI
> triggers (i.e.: on delete restrict - cannot delete question because answer
> exists).
>
> As well, there are some who get really alarmed when they find out this is
> how we sometimes do things, but the fact of the matter is that we aren't
> dealing with data whose integrity is 100% critical. If there are some "lost
> puppies" in a table (like answers without questions) it's not going to
> affect functionality, at least I've never seen it do so (I'm probably going
> to get told I'm a bad dba for even saying that - and if I am I want to know
> it!).
>
> The rational is because of short development timelines they find it
> impossible to allow application logic/flow to be fully developed inside the
> database, therefore they prefer to keep it in one location where it can be
> managed: the code (Java, ASP, C++).
>
> These are not massively complex databases. For the most part they average 50
> tables and the relationships are fairly straightforward, but there are many
> thousands of rows and growing constantly.
>
> I'm interested in what others have to say about this approach. I'm currently
> without a senior DBA to mentor me :-(
>
> Regards
> Kate
>
> --------
> If you're bored, then visit the list's website: http://www.lazydba.com (updated daily)
> to unsubscribe, send a blank email to oracledba-unsubscribe_at_quickdoc.co.uk
> to subscribe send a blank email to oracledba-subscribe_at_quickdoc.co.uk
>
>
>
> --------
> If you're bored, then visit the list's website: http://www.lazydba.com (updated daily)
> to unsubscribe, send a blank email to oracledba-unsubscribe_at_quickdoc.co.uk
> to subscribe send a blank email to oracledba-subscribe_at_quickdoc.co.uk
>

--
Mark Saltzman
Assistant Director - Information Systems
University of Wisconsin-Extension