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.
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 . . . .
Stevens) wrote: >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.
>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 . . . .
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
A Modest Proposal (ala Swift) 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" <spamd...@nospam.noway.nohow> wrote in message
> 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.
> 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 . . . .
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
If the application is implemented correctly, it should not be apparent 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.
> 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.
> 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 . . . .
On Thu, 21 Mar 2002 08:59:22 -0500, Peter Sylvester <pet...@mitre.org> wrote: >If the application is implemented correctly, it should not be apparent >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 wrote:
>> 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.
>> 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 . . . .
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.)
Ed Stevens wrote: > 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.
> 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 . . . .
Ed Stevens wrote: > On Thu, 21 Mar 2002 08:59:22 -0500, Peter Sylvester <pet...@mitre.org> wrote:
> >If the application is implemented correctly, it should not be apparent > >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 wrote:
> >> 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.
> >> 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 . . . .
> 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.)
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.
> On Thu, 21 Mar 2002 08:59:22 -0500, Peter Sylvester <pet...@mitre.org> wrote:
> >If the application is implemented correctly, it should not be apparent > >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 wrote:
> >> 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.
> >> 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 . . . .
> 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.)
It will be hard to argue with your partner on DBA grounds...
... because he/she is not a DBA - they're an idiot :-)
> A Modest Proposal (ala Swift) > 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" <spamd...@nospam.noway.nohow> wrote in message > news:3c98f481.201021974@ausnews.austin.ibm.com... > > 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.
> > 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 . .
A major drawback of using RI is that it tends to make your application 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....
On Wed, 20 Mar 2002, spamd...@nospam.noway.nohow wrote: > 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.
On Thu, 21 Mar 2002, spamd...@nospam.noway.nohow wrote: > 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 deForest Boyer Sweet dreams and flying machines in pieces on the ground.
On Fri, 22 Mar 2002, d...@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.
> 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?
-- Galen deForest Boyer Sweet dreams and flying machines in pieces on the ground.
> On Fri, 22 Mar 2002, d...@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 ?
> > 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?
> -- > Galen deForest Boyer > Sweet dreams and flying machines in pieces on the ground.
The 'downsides' are that you have to take care in your database design. You 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" <spamd...@nospam.noway.nohow> wrote in message
> On Thu, 21 Mar 2002 08:59:22 -0500, Peter Sylvester <pet...@mitre.org> wrote:
> >If the application is implemented correctly, it should not be apparent > >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 wrote:
> >> 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.
> >> 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 . . . .
> 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.)
> 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.
-- 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
> 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.
>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.
>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 . . . .
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.
>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.
>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 . . . .
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.
Don Smy wrote: > A major drawback of using RI is that it tends to make your application > 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....
> A major drawback of using RI is that it tends to make your application > 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....
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.
> > A major drawback of using RI is that it tends to make your application > > 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....
> 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.
> "Galen Boyer" <galenbo...@hotpop.com> wrote in message > news:ubsdhayyg.fsf@rcn.com... >> On Fri, 22 Mar 2002, d...@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?
|
