RI - pros and cons
Ed Stevens
City. I'm getting caught between my "partner" DBA (with whom I have many
philosophical differences) and the developers.
New application being developed. Four rather simple tables - header data,
detail data, and a couple of reference/look-up tables. Developers want RI and I
agree. DBA says "RI is more trouble than its' worth. You should take care of
it in your application." I'm caught in the middle, stongly disagreeing (that's
putting it diplomaticly) with the other DBA but having to maintain a working
relationship. He makes the claim "all the other DBA's I read on MetaLink say RI
is too much trouble" but won't produce evidence.
Anyone want to comment on the technical merits of RI vs. not. I've always felt
that the benefits of RI (and normalized tables) was so obvious as to not even
require any further justification. Do I need to be re-educated, or . . . .
Sybrand Bakker
hire a new DBA
Regrettably there are too many commercial sw packages on the market
which do not implement RI at the database side.
The disadvantages of implementing RI elsewhere are obvious
- it results in fat clients
- it results in non-centralized maintenance of RI code
- it results in much slower RI-checking, as the RI-checking in the
kernel usually doesn't use explicit sql-statements
- it may result in locking issues
etc etc etc
Regards
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address
Jim Kennedy
Since it is a complex matter to specify different data types in a table
there should be just one data type. Additionally, often complex naming
conventions (eg birthdate, firstname, etc.) can lead to a great deal of
debate and wasted meeting time and conflict on column and table names.
Therefore, the following database programming practices should be standard:
1, All columns will be varchar2(4000). This simplifies having to deal
with the variety of data types and sizes. Varchar2 is a variable length
field and so only takes up as much space as the actual data occupies. All
other data types can be represented as varchar2. Users don't really need
more than 4000 bytes in a field so 4000 is fine.
2. The names of the columns will be the numeric position in the table.
So the first column will be 1, the second 2, etc until the maximum is 1000.
3. All columns will not have any constraints, null or default
conditions. The presence of not null conditions and constraints is just a
limit on what can go in the column. This practice generates too many errors
and makes it too difficult for users to add data to the system.
4. All tables will be named based upon the order in which they are
created. The first table will be 1, the second 2 etc.
5. There will be only one user , sys and the password will be shortened
from the default change_on_install to just sys. Much easier to remember,
shorter, and no one will get an error because they don't have rights to do
what they need to do. All users will just use the sys password. This cuts
down on the calls to internal company support about lost or forgotten
passwords.
6. To maintain performance the database must be run in non-archivelog
mode.
7.Since it is too complex to use bind variables in applications only
hard parsed SQL will be allowed.
8. Since it is confusing to program transactions all sql statements will
be followed by the commit command (except the commit command).
These "best practices" should significantly cut down on development time and
save money.
Jim
"Ed Stevens" <spam...@nospam.noway.nohow> wrote in message
news:3c98f481....@ausnews.austin.ibm.com...
Frank
If you have a master detail relationship in your application, all child-rows
must(perhaps your DBA does not agree here either :-) be deleted when a
master row is deleted.
In his soulution this upkeep must be done in all forms/package whatever you
use. If you later on add a new child table (to solve new business
requirements) to stay to his philosophy, you must fix this in all your
business logic. If you add a new child table to your table that already is a
child table, all logic must be fixed to solve this new upkeep.
As with all strict rules there can be exceptions, I think interface tables
can be easier to deal with without RI.
By interface tables in this context I mean tables that are populated e.g
from a file and need a (lot of)
logic in order to get into a RI shape, before these tables are used by a
business logic to populate the actual
application tables.
--
Frank
Postings are my personal opinions/views.
I'm not speaking on behalf of any company/organization/institution.
If the posting is mission version numbers; assume:
Oracle v9.0.1.0.1/JDK1.3.1_02
Peter Sylvester
whether RI exists in the DB or not. Putting it in the DB will keep the
application honest. From what I have seen, without RI in the database,
you can kiss integrity good-bye.
-Peter
Ed Stevens
Which is exactly my postion. But the other DBA keeps claiming that RI is "more
trouble than it is worth" and that his position is backed up by "many other
DBA's" He won't give any specifics about the downside to RI, so I thought I'd
ask here if anyone knows of any downsides. I think he's just blowing smoke, but
I wanted to make sure I'd done MY research to eliminate the chance that I might
be overlooking something myself.
--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)
damorgan
Suggest that he/she find another profession.
There is another word for a database lacking in RI: I won't use that word here.
Daniel Morgan
damorgan
I've 33 years in the profession. And not once have I heard any senior DBA support your
partner's position except in the case of a data warehouse with read-only tablespaces.
Daniel Morgan
Connor McDonald
It will be hard to argue with your partner on DBA grounds...
... because he/she is not a DBA - they're an idiot :-)
Cheers
Connor
--
==============================
Connor McDonald
"Some days you're the pigeon, some days you're the statue..."
Paul Brewer
news:f_em8.82030$ZR2....@rwcrnsc52.ops.asp.att.net...
Ah, the Peoplesoft philosophy...
Paul
Don Smy
database vendor specific. If you intend building apps that will be used by
different brands of databases I'd say putting the RI in your app is a good
thing.
Another reason for not using RI is that RI is designed to stop people who
aren't experts from doing stupid things. Quite often there are very good
reasons for circumventing RI in the application (particularly during data
conversion projects or providing data importing facilities). For conversions
I guess you could just drop the constraints, do the conversion and then put
them back on. For importing it may not be that easy.
If none of these issues apply to your environment then I think your DBA just
doesn't like work....
Galen Boyer
> DBA says "RI is more trouble than its' worth. You should take care of
> it in your application."
This guy isn't even a database guy, much less a DBA. What was one of
the number one reasons for relational databases to come about in the
first place? For data integrity! How do you guarantee it? By using
RI.
He's plain wrong.
--
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.
Galen Boyer
> Which is exactly my postion. But the other DBA keeps claiming that RI
> is "more trouble than it is worth" and that his position is backed up
> by "many other DBA's"
I doubt this highly. Have him point you to specific DBA's thoughts
saying this.
> He won't give any specifics about the downside to RI,
There are performance hits. The database has to check the parent tables
to see that the keys exist.
> so I thought I'd ask here if anyone knows of any downsides. I think
> he's just blowing smoke
No, he's smoking something. I'd only agree with him beside a campfire
as we all passed around a big spleef.
Galen Boyer
> A major drawback of using RI is that it tends to make your application
> database vendor specific.
How is this? It is completely transparent to the application.
> If you intend building apps that will be used by different brands of
> databases I'd say putting the RI in your app is a good thing.
Once again. How is this?
> Another reason for not using RI is that RI is designed to stop people
> who aren't experts from doing stupid things.
Wrong. It is designed to protect the data. All people of all levels do
"stupid" things.
> Quite often there are very good reasons for circumventing RI in the
> application (particularly during data conversion projects or providing
> data importing facilities). For conversions I guess you could just
> drop the constraints, do the conversion and then put them back on.
Yes. This is a reason for disabling them. But you still should
reenable them after you are done. Of course, that is a whole other can
of worms cause Oracle drops the indexes that are used to support the RI
constraints. What are the correct storage clauses of the recreated
indexes?
Jim Kennedy
Love it Paul, haven't used Peoplesoft, but I understand.
Jim
"Paul Brewer" <pa...@paul.brewers.org.uk> wrote in message
news:3c9a4...@mk-nntp-1.news.uk.worldonline.com...<snip>
Ah the Peoplesoft philosophy...
>
> Paul
>
>
Don Smy
"Galen Boyer" <galen...@hotpop.com> wrote in message
news:ubsdha...@rcn.com...
> On Fri, 22 Mar 2002, do...@bimtech.com.au wrote:
> > A major drawback of using RI is that it tends to make your application
> > database vendor specific.
>
> How is this? It is completely transparent to the application.
No it isn't transparent to the app. Databases implement RI with differing
levels of functionality.
What you are saying is that you can develop an application and the way the
database interacts with your app will be the same regardless of the brand.
At the very least you would need to deal with different return codes and
errors as in most cases, different databases behave quite differently when
RI rules are broken.
>
> > If you intend building apps that will be used by different brands of
> > databases I'd say putting the RI in your app is a good thing.
>
> Once again. How is this?
Because you can cater for the RI deficiencies in poor databases, deal with
differences in how the various vendor's API return RI errors, handle RI
accross brands, etc....
>
> > Another reason for not using RI is that RI is designed to stop people
> > who aren't experts from doing stupid things.
>
> Wrong. It is designed to protect the data. All people of all levels do
> "stupid" things.
Agreed that programmers do get things wrong but my point was that they do
however have the opportunity to test things before they deploy their code so
the applications they build don't do stupid things.After testing the
application should be stable enough not to corrupt the data. RI is only
really needed because there are so many tools available to users that can
access the application's data and not be governed by an application's
business rules . And if you still disagree that RI can't be left up to the
programmer, who puts the RI constraints on the database in the first place ?
Isn't there just as much margin for error here ?
Niall Litchfield
have to code some more to ensure that any old rubbish doesn't get in there.
You have to document your system with (at least) an ERD. You have to
carefully define your business logic. Initial development time is thus
increased. The alternative is to just bung a few create table scripts
together stuff some data in and write some web pages to query the data and
do ad hoc inserts etc. This is commonly known as rapid application
development, or even better development in internet time.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************.
"Ed Stevens" <spam...@nospam.noway.nohow> wrote in message
news:3c99ea81....@ausnews.austin.ibm.com...
Niall Litchfield
news:G8vm8.18178$Hz2....@news-server.bigpond.net.au...
> A major drawback of using RI is that it tends to make your application
> database vendor specific.
Of course some of us, and I would be one, would argue that that is a good
thing.
Just one example.
Our main app create a whole bunch of permanent tables in the database during
its transaction processing to hold things like intermediate results etc. At
the end of the process it drops them. This is a classic cross platform
approach guranteed to work on all the RDBMS systems the vendor supports. In
our case the app runs on 8i so the potential for massive performance gains
using global temporary tables is huge. The app will *never* use this feature
because it has chosen to avoid the 'limitation' of being vendor specific.
Being vendor specific is not a technical limitation but a marketing one.
Brian Dick
> I feel stupid even posting this question but there's trouble brewing in
River
> City. I'm getting caught between my "partner" DBA (with whom I have many
> philosophical differences) and the developers.
Let the company developers fight the battle. If they won't do it, then
document your recommendation, make sure management and other witnesses see
the docs, and go with the flow. Also, make sure you get them to identify the
accountable party and make sure it is not you.
If problems arise later, and you are still around, resist the "I told you
so". Fixing a problem in production costs ten times what it would have cost
to prevent it in development. Just have a good laugh as you cash the big
check you got from fixing the production problem.
>
> New application being developed. Four rather simple tables - header data,
> detail data, and a couple of reference/look-up tables. Developers want RI
and I
> agree. DBA says "RI is more trouble than its' worth. You should take
care of
> it in your application."
What he really is saying is that he doesn't want the responsiblity of
maintaining integrity in the database. So, if the application screws up, he
is not held accountable.
Regardless of where you implement RI, try to keep the rules in one place. If
RI is spread all over the place, it becomes difficult to maintain
consistently. This is probably the number one reason to put it in the
database. Also, if it is in the database, it can't be easily circumvented.
> I'm caught in the middle, stongly disagreeing (that's
> putting it diplomaticly) with the other DBA but having to maintain a
working
> relationship. He makes the claim "all the other DBA's I read on MetaLink
say RI
> is too much trouble" but won't produce evidence.
Heresay is not admissible evidence. Counter by saying that the DBA's that
screw up their data because they don't use RI would not dare to publicly
admit it on MetaLink.
> Anyone want to comment on the technical merits of RI vs. not. I've always
felt
> that the benefits of RI (and normalized tables) was so obvious as to not
even
> require any further justification. Do I need to be re-educated, or . . .
.
To RI or not to RI is not a technical issue. It is about cost effectively
managing risk. If the application screws up and it doesn't matter, then
little effort should be put into RI. If the application screws up and it
bankrupts the company, then a reasonable effort should be expended to ensure
RI.
--
Later,
BEDick
Ed Stevens
wrote:
>I feel stupid even posting this question but there's trouble brewing in River
I'd like to thank everyone for their responses, which were about what I
expected. If nothing else, I needed some moral support and reinforcement that I
wasn't going crazy.
Ed Stevens
wrote:
>I feel stupid even posting this question but there's trouble brewing in River
damorgan
totally misunderstand the prpose of RI.
Any database, other than one sitting in a read-only tablespace without RI, is
one I will refuse to work on ... except to fix it.
Daniel Morgan
Connor McDonald
This may be true...but I've found that any product that is developed to
be database-independent generally ends up running poorly on all the
target platforms. And you get great things like:
"Yes its database independent, but you have to use ODBC drivers from
company X, and is has to be deployed on Windows version Y"
which makes the "independent" concept somewhat moot.
hth
connor
damorgan
and "it performs like road kill".
Daniel Morgan
Galen Boyer
>
> "Galen Boyer" <galen...@hotpop.com> wrote in message
> news:ubsdha...@rcn.com...
>> On Fri, 22 Mar 2002, do...@bimtech.com.au wrote:
>> > A major drawback of using RI is that it tends to make your
>> > application database vendor specific.
>>
>> How is this? It is completely transparent to the application.
>
> No it isn't transparent to the app. Databases implement RI with
> differing levels of functionality. What you are saying is that
> you can develop an application and the way the database
> interacts with your app will be the same regardless of the
> brand. At the very least you would need to deal with different
> return codes and errors as in most cases, different databases
> behave quite differently when RI rules are broken.
Ah, yes, you are correct. I was thinking completely along the
lines of the RI protecting your data and the app not having to
worry about it.
If you want independence, this should be a place where it
shouldn't be that difficult, in relative terms, to make it
independent by putting a layer that sends your application back a
generic code it knows how to handle. You then need to deal with
DB specific codes as outliers.
>> > If you intend building apps that will be used by different
>> > brands of databases I'd say putting the RI in your app is a
>> > good thing.
>>
>> Once again. How is this?
>
>
> Because you can cater for the RI deficiencies in poor
> databases, deal with differences in how the various vendor's
> API return RI errors, handle RI accross brands, etc....
Who died and made the application boss anyways. :-)
>> > Another reason for not using RI is that RI is designed to
>> > stop people who aren't experts from doing stupid things.
>>
>> Wrong. It is designed to protect the data. All people of all
>> levels do "stupid" things.
>
> Agreed that programmers do get things wrong but my point was
> that they do however have the opportunity to test things before
> they deploy their code so the applications they build don't do
> stupid things.After testing the application should be stable
> enough not to corrupt the data.
But your application cannot guarantee it, while a DBA can.
"Stable enough" isn't stable enough. It has to be completely
rock solid, and no DBA is going to believe you when you walk off
the street saying your app is fine, don't worry about
constraints.
> RI is only really needed because there are so many tools
> available to users that can access the application's data and
> not be governed by an application's business rules .
This should be enough for you, as an application developer, not
to even go down this route. Even if you do have completely rock
solid RI, a DBA ain't going to trust the next guy, so you, in the
end, wasted your time cause all your RI work will be duplicated
on the database side, cause the dba says, no way hosey.
> And if you still disagree that RI can't be left up to the
> programmer, who puts the RI constraints on the database in the
> first place ? Isn't there just as much margin for error here ?
No. There are a boatload of tools that are centered around this
to make this a rocksolid process. How many tools are written to
help you fix your RI issues?
--
Galen Boyer