Re: FK Constraints
Hi All, Nobody has proposed this before so let me throw it out for discussion. Think about a compromise between having and not having referential integrity constraints. The approach is that during development and testing, all RIs are enabled. But when you push to production, disable or drop all of them. The advantage of not having or enabling RIs in production is not portability in this case; it's performance instead, however marginal it is. I think I read somewhere about Oracle's official answer to this little performance hit due to RIs. Think about it. There must be some. It shouldn't be hard to measure but I have not done it. Yong Huang [EMAIL PROTECTED] you wrote: We have a situation where are no relationships are defined at the database level. i.e no foreign keys constraints have established at the Database. The application is still at the Development Stage. Everything is controlled at the application level. I as the DBA appose this design for Data security and also cannot reverse engineer from the tables into Designer. Can you please share you pros / Cons. Thanks __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: FK Constraints
Title: Building FK constraints depends on the application characteristic. We have an application in which we do not have any FK constraints. The primary purpose of this application is monitoring the trade messages that flow from stock exchanges and match them with the stock purchase/buy orders. Only 6 tables are in the database. But we receive approximately 1000 to 10,000 trade messages (depending on the customer) into one of the main tables. The average size of a trade message is 800 bytes. Once a trade is matched, the record is either deleted or archived depending on business rules. Stored procedures validate the trade message but they do not use any FK while inserting the data or deleting the data.In the beginning building this kind of database gave me creeps and later once I understood the business rules/functionality, I was convinced. This application is running without any problem (I mean data inconsistency, leaving orphan records in the tables etc.) and customers are using this and they are satisfied.The database is 804 on Sun.Maheswara RaoDBA [EMAIL PROTECTED]
Re: FK Constraints
I don't get new user requests.. I get requests from end-users for data. But I understand. And I know that it's a question of coming in with the baseball bat and assorted heavy weaponry and making a stand (or as my CTO is fond of saying "putting a stake in the ground") Just haven't collected all the cudgels yet :) >From: Rodd Holman <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: Re: FK Constraints >Date: Tue, 20 Mar 2001 07:35:32 -0800 > >Rachel, >One way I got this across to my developers and users was in creating new >users for the DB. Every time I got a new user request, I would forward it >to >the director of the group that officially "owns" the data to get his >permission to allow them in HIS data. After a few round robins of this the >people finally realized that when I said Ops, Acctg, etc. owned the data, I >also made them responsible for THEIR data. We still have fun every now >and >then with "Can you recover this for me" type reqests, but most of the time >they recognize that I keep the system going and they keep the data correct. > >On Monday 19 March 2001 18:10, you wrote: > > the DBA is not responsible for the data?? Could you PLEASE come to my > > office and explain that (I'll bring the heavy club) to my developers, >users > > and management? > > > > >-- >Rodd Holman >Oracle DBA >(605) 988-1373 >[EMAIL PROTECTED] >Comments made are my own opinions and views. They do not represent views, >policies, or procedures of LodgeNet Entertainment Corporation > > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Rodd Holman > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: FK Constraints
Rachel, One way I got this across to my developers and users was in creating new users for the DB. Every time I got a new user request, I would forward it to the director of the group that officially "owns" the data to get his permission to allow them in HIS data. After a few round robins of this the people finally realized that when I said Ops, Acctg, etc. owned the data, I also made them responsible for THEIR data. We still have fun every now and then with "Can you recover this for me" type reqests, but most of the time they recognize that I keep the system going and they keep the data correct. On Monday 19 March 2001 18:10, you wrote: > the DBA is not responsible for the data?? Could you PLEASE come to my > office and explain that (I'll bring the heavy club) to my developers, users > and management? > -- Rodd Holman Oracle DBA (605) 988-1373 [EMAIL PROTECTED] Comments made are my own opinions and views. They do not represent views, policies, or procedures of LodgeNet Entertainment Corporation -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodd Holman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: FK Constraints
At Tufts,in our service agreement document. we clearly make the note "DBA is not the owner of the content of the data. DBA is responsible for the maintenance, performance, safety, backup and recover and connectivity issues. So if FM needs reference HR data, they have to go HR to get approval first. wrong data? sorry, we are not responsible for that. But we still get the bites, since we have to recover the table for them. Joan -Original Message- Sawmiller Sent: Tuesday, March 20, 2001 7:32 AM To: Multiple recipients of list ORACLE-L Precisely...I always love the look on people's faces when I tell them I really don't give two hoots about their data...then I explain as Jared did so eloquently... >>> [EMAIL PROTECTED] 03/19/01 09:45PM >>> OK, let me rephrase. The DBA is not responsible for the *content* of the data. The DBA *is* responsible for the safety, recoverability and integrity ( where possible ) of the data. The DBA is ont resposible for the data's content, except where the DBA is responsible for constraints that place some controls on the data content. If one of the developers enters incorrect data into an application, and then wants to know why the database is broken, I will tactfully ( maybe ) inform him/her of the error in his/her ways, and will help that person correct the problem. Do I get to swing the club? ;) Jared On Mon, 19 Mar 2001, Rachel Carmichael wrote: > the DBA is not responsible for the data?? Could you PLEASE come to my office > and explain that (I'll bring the heavy club) to my developers, users and > management? > > They seem to believe that my primary function is to dig information out of > the database for them. Backup and recovery? Capacity planning? Why on earth > would I want to do THAT? > > > >From: [EMAIL PROTECTED] > >Reply-To: [EMAIL PROTECTED] > >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > >Subject: RE: FK Constraints > >Date: Mon, 19 Mar 2001 13:20:34 -0800 > > > > > >I'll have to disagree with this. I've seen to many > >programs break when the constraints were enabled. > > > >Developers generally don't have a clue how to maintain > >integrity in the application data. This is not to impugn > >all developers, just about 90% of them. > > > >The database knows how to enforce integrity, developers don't. > > > >As a DBA I insist on RI in the database. > > > >When there is no RI in the database, DBA's are constantly > >being called for 'database problems' when the problem is > >actually in the data, which is not our responsibility. > > > > > >The DBA spends much time proving the source of the problem > >since the developer is unable to. > > > >More fodder for duhveloper.com. > > > >Jared > > > >On Mon, 19 Mar 2001, Holman, Rodney wrote: > > > > > You are running into a primary point of contention between many DBA's > >and > > > developers. Dev's don't like the use of PK/FK relationships within the > > > database because it is not always portable across multiple RDBMS's. By > > > making the app handle all the referential integrity issues they can say > > > "Yes, our system runs on Oracle, Sybase, Informix, MS SQL Server, or > >even > > > Access " All they need the RDBMS to do is store tables and indexes > >then. > > > Many third party apps use this approach. The problem is when their code > >is > > > correct and perfect, they are right. You don't NEED FK's. However I > >have > > > yet to run across any super human developer that codes everything > >perfectly. > > > What happens... you get duplicate key values and child tables filled > >with > > > orphans because somewhere in the code a restriction was missed. By > > > assigning these at the DB level you can forget about having to maintain > >it > > > in the app. The RDBMS does it for you. Push for RDBMS level control of > > > this. It will save you many headaches later over data corruption. > > > > > > Rodd Holman > > > > > > -Original Message- > > > Sent: Monday, March 19, 2001 12:05 PM > > > To: Multiple recipients of list ORACLE-L > > > > > > > > > Hi all: > > > > > > We have a situation where are no relationships are > > > defined at the database level. i.e no foreign keys > > > constraints have established at the Database. The > > > application is still at the Development Stage. > > > > > > Everything is
Re: FK Constraints
Ramani: I live daily with the negative effects of referential integrity enforced (sometimes) at the application level. From my standpoint, there have been no "pros" to this method. I need to use a lot of data from our mainframe DB2 database, where all of the RI is written into their applications. Now I access the data from another avenue - Oracle's transparent gateway - and there is no way for me to tell what tables should join, what the valid values are, and even what codes within a column represent! Of course, when they designed this database 10 years ago, they never took into consideration that another database would access this data; they only thought that their COBOL programs would touch the data. Years later, programmers have moved on, the world has changed, but their database stays the same. The have one huge LOV table where they store codes based on what program/screen needs them (as far as I can figure). This entire design shows a lack of vision. Sorry to ramble, but this is a sore point with me. The bottom line here is that every time someone else needs to access the data, it takes at least 5 times longer to understand the relationships and make things work. And, we are never sure that what we are getting is right. I have at least 20 scientists accessing this data. Imagine not only the productivity that is lost, but can we be sure that the results produced from the analysis of this data is correct based on its lack of credibility? I would never, ever, ever, ever agree to a database without referential integrity imposed by the RDBMS that was in my control. I thank you for the opportunity to vent (-_-) ___ Sherrie Kubis Southwest Florida Water Management District 2379 Broad Street Brooksville FL 34604-6899 Phone: (352) 796-7211, Ext. 4033 Fax: (352) 754-6776 Email: Mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: FK Constraints
Precisely...I always love the look on people's faces when I tell them I really don't give two hoots about their data...then I explain as Jared did so eloquently... >>> [EMAIL PROTECTED] 03/19/01 09:45PM >>> OK, let me rephrase. The DBA is not responsible for the *content* of the data. The DBA *is* responsible for the safety, recoverability and integrity ( where possible ) of the data. The DBA is ont resposible for the data's content, except where the DBA is responsible for constraints that place some controls on the data content. If one of the developers enters incorrect data into an application, and then wants to know why the database is broken, I will tactfully ( maybe ) inform him/her of the error in his/her ways, and will help that person correct the problem. Do I get to swing the club? ;) Jared On Mon, 19 Mar 2001, Rachel Carmichael wrote: > the DBA is not responsible for the data?? Could you PLEASE come to my office > and explain that (I'll bring the heavy club) to my developers, users and > management? > > They seem to believe that my primary function is to dig information out of > the database for them. Backup and recovery? Capacity planning? Why on earth > would I want to do THAT? > > > >From: [EMAIL PROTECTED] > >Reply-To: [EMAIL PROTECTED] > >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > >Subject: RE: FK Constraints > >Date: Mon, 19 Mar 2001 13:20:34 -0800 > > > > > >I'll have to disagree with this. I've seen to many > >programs break when the constraints were enabled. > > > >Developers generally don't have a clue how to maintain > >integrity in the application data. This is not to impugn > >all developers, just about 90% of them. > > > >The database knows how to enforce integrity, developers don't. > > > >As a DBA I insist on RI in the database. > > > >When there is no RI in the database, DBA's are constantly > >being called for 'database problems' when the problem is > >actually in the data, which is not our responsibility. > > > > > >The DBA spends much time proving the source of the problem > >since the developer is unable to. > > > >More fodder for duhveloper.com. > > > >Jared > > > >On Mon, 19 Mar 2001, Holman, Rodney wrote: > > > > > You are running into a primary point of contention between many DBA's > >and > > > developers. Dev's don't like the use of PK/FK relationships within the > > > database because it is not always portable across multiple RDBMS's. By > > > making the app handle all the referential integrity issues they can say > > > "Yes, our system runs on Oracle, Sybase, Informix, MS SQL Server, or > >even > > > Access " All they need the RDBMS to do is store tables and indexes > >then. > > > Many third party apps use this approach. The problem is when their code > >is > > > correct and perfect, they are right. You don't NEED FK's. However I > >have > > > yet to run across any super human developer that codes everything > >perfectly. > > > What happens... you get duplicate key values and child tables filled > >with > > > orphans because somewhere in the code a restriction was missed. By > > > assigning these at the DB level you can forget about having to maintain > >it > > > in the app. The RDBMS does it for you. Push for RDBMS level control of > > > this. It will save you many headaches later over data corruption. > > > > > > Rodd Holman > > > > > > -Original Message- > > > Sent: Monday, March 19, 2001 12:05 PM > > > To: Multiple recipients of list ORACLE-L > > > > > > > > > Hi all: > > > > > > We have a situation where are no relationships are > > > defined at the database level. i.e no foreign keys > > > constraints have established at the Database. The > > > application is still at the Development Stage. > > > > > > Everything is controlled at the application level. > > > > > > I as the DBA appose this design for Data security and > > > also cannot reverse engineer from the tables into > > > Designer. > > > > > > Can you please share you pros / Cons. > > > > > > Thanks > > > > > > > > > > > > > > > > > > __ > > > Do You Yahoo!? > > > Get email at your own domain with Yahoo! Mail.
Re: FK Constraints
Rachel Carmichael wrote: > > the DBA is not responsible for the data?? Could you PLEASE come to my office > and explain that (I'll bring the heavy club) to my developers, users and > management? > > They seem to believe that my primary function is to dig information out of > the database for them. Backup and recovery? Capacity planning? Why on earth > would I want to do THAT? you sure we don't work of rhte same company?;-) [don't i just wish.;-)] and then there's [at 10AM] "oh bill we need a new ORACLE instance up and running for us." "ok, when do you need it by?" "noon." -- Bill Thater Certifiable ORACLE DBA Telergy, Inc [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thater, William INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: FK Constraints
OK, let me rephrase. The DBA is not responsible for the *content* of the data. The DBA *is* responsible for the safety, recoverability and integrity ( where possible ) of the data. The DBA is ont resposible for the data's content, except where the DBA is responsible for constraints that place some controls on the data content. If one of the developers enters incorrect data into an application, and then wants to know why the database is broken, I will tactfully ( maybe ) inform him/her of the error in his/her ways, and will help that person correct the problem. Do I get to swing the club? ;) Jared On Mon, 19 Mar 2001, Rachel Carmichael wrote: > the DBA is not responsible for the data?? Could you PLEASE come to my office > and explain that (I'll bring the heavy club) to my developers, users and > management? > > They seem to believe that my primary function is to dig information out of > the database for them. Backup and recovery? Capacity planning? Why on earth > would I want to do THAT? > > > >From: [EMAIL PROTECTED] > >Reply-To: [EMAIL PROTECTED] > >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > >Subject: RE: FK Constraints > >Date: Mon, 19 Mar 2001 13:20:34 -0800 > > > > > >I'll have to disagree with this. I've seen to many > >programs break when the constraints were enabled. > > > >Developers generally don't have a clue how to maintain > >integrity in the application data. This is not to impugn > >all developers, just about 90% of them. > > > >The database knows how to enforce integrity, developers don't. > > > >As a DBA I insist on RI in the database. > > > >When there is no RI in the database, DBA's are constantly > >being called for 'database problems' when the problem is > >actually in the data, which is not our responsibility. > > > > > >The DBA spends much time proving the source of the problem > >since the developer is unable to. > > > >More fodder for duhveloper.com. > > > >Jared > > > >On Mon, 19 Mar 2001, Holman, Rodney wrote: > > > > > You are running into a primary point of contention between many DBA's > >and > > > developers. Dev's don't like the use of PK/FK relationships within the > > > database because it is not always portable across multiple RDBMS's. By > > > making the app handle all the referential integrity issues they can say > > > "Yes, our system runs on Oracle, Sybase, Informix, MS SQL Server, or > >even > > > Access " All they need the RDBMS to do is store tables and indexes > >then. > > > Many third party apps use this approach. The problem is when their code > >is > > > correct and perfect, they are right. You don't NEED FK's. However I > >have > > > yet to run across any super human developer that codes everything > >perfectly. > > > What happens... you get duplicate key values and child tables filled > >with > > > orphans because somewhere in the code a restriction was missed. By > > > assigning these at the DB level you can forget about having to maintain > >it > > > in the app. The RDBMS does it for you. Push for RDBMS level control of > > > this. It will save you many headaches later over data corruption. > > > > > > Rodd Holman > > > > > > -Original Message- > > > Sent: Monday, March 19, 2001 12:05 PM > > > To: Multiple recipients of list ORACLE-L > > > > > > > > > Hi all: > > > > > > We have a situation where are no relationships are > > > defined at the database level. i.e no foreign keys > > > constraints have established at the Database. The > > > application is still at the Development Stage. > > > > > > Everything is controlled at the application level. > > > > > > I as the DBA appose this design for Data security and > > > also cannot reverse engineer from the tables into > > > Designer. > > > > > > Can you please share you pros / Cons. > > > > > > Thanks > > > > > > > > > > > > > > > > > > __ > > > Do You Yahoo!? > > > Get email at your own domain with Yahoo! Mail. > > > http://personal.mail.yahoo.com/ > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > -- > > > Author: ramani akhil > > > INET: [EMAIL PROTECTED] >
Re: FK Constraints (fwd)
-- Forwarded message -- Date: Mon, 19 Mar 2001 18:29:42 -0800 (PST) To: Rodd Holman <[EMAIL PROTECTED]> Rodd, I understand completely. Third party apps often do their best to complicate a DBA's life. Sorry for jumping in with both feet. :) Jared On Mon, 19 Mar 2001, Rodd Holman wrote: > Jared and Jaques, > I agree with you completely. RI should always be enforced at the DB level. > I was just sharing my experience with a couple of 3rd party sotfware vendors > that wouldn't allow me to put the constraints in the DB. In fact one of them > had a lookup table hardcoded into the app interface. If you tried to enforce > RI in the DB it would fail looking for parents on one of the child tables to > this hardcoded table. (To stop any disparging of my credibility I had NO say > in the purchasing of this RI nightmare. I was "gifted" with the > "opportunity to excel" when the !@#$%& that stuck us with the app quit the > company.) Their sole reason for the approach (valid or not) was db > portability. Sadly small software shops crop up to fill niche needs and do > not necessarily employ people who understand the inner workings of RDBMS's. > They know how to make their cute forms and flashy reports using some wizbang > dev tool and totally ignore the fact that a real foundation is need for > proper functionality. > > On Monday 19 March 2001 15:21, you wrote: > > I'll have to disagree with this. I've seen to many > > programs break when the constraints were enabled. > > > > Developers generally don't have a clue how to maintain > > integrity in the application data. This is not to impugn > > all developers, just about 90% of them. > > > > The database knows how to enforce integrity, developers don't. > > > > As a DBA I insist on RI in the database. > > > > When there is no RI in the database, DBA's are constantly > > being called for 'database problems' when the problem is > > actually in the data, which is not our responsibility. > > > > > > The DBA spends much time proving the source of the problem > > since the developer is unable to. > > > > More fodder for duhveloper.com. > > > > Jared > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: FK Constraints
> I'm a beginner too, but without a table FK (relation) how you > make a join > statements ? Do you normalize your tables ? since when do u require FK's to make a join? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandar Ghosalkar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: FK Constraints
Joins do not require the presence of FK's in order to work properly. Jared On Mon, 19 Mar 2001 [EMAIL PROTECTED] wrote: > Hi, > > I'm a beginner too, but without a table FK (relation) how you make a join > statements ? Do you normalize your tables ? > > The relation model is concerned with logical maters only, not physical > matters. It addresses three aspects of data, data stucture (object) data > integrity, and data manipulation. > The Object (Tables), > Integrity portion has to do with PK and FK > Data manipulation (SELECT, JOIN etc) > > In addition I think with the Normalized database you will have smaller > database and easy to maintain (base on object). > > > > -Original Message- > akhil > Sent: Tuesday, 20 March 2001 2:05 AM > To: Multiple recipients of list ORACLE-L > > > Hi all: > > We have a situation where are no relationships are > defined at the database level. i.e no foreign keys > constraints have established at the Database. The > application is still at the Development Stage. > > Everything is controlled at the application level. > > I as the DBA appose this design for Data security and > also cannot reverse engineer from the tables into > Designer. > > Can you please share you pros / Cons. > > Thanks > > > > > > __ > Do You Yahoo!? > Get email at your own domain with Yahoo! Mail. > http://personal.mail.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: ramani akhil > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: FK Constraints
Hi, I'm a beginner too, but without a table FK (relation) how you make a join statements ? Do you normalize your tables ? The relation model is concerned with logical maters only, not physical matters. It addresses three aspects of data, data stucture (object) data integrity, and data manipulation. The Object (Tables), Integrity portion has to do with PK and FK Data manipulation (SELECT, JOIN etc) In addition I think with the Normalized database you will have smaller database and easy to maintain (base on object). -Original Message- akhil Sent: Tuesday, 20 March 2001 2:05 AM To: Multiple recipients of list ORACLE-L Hi all: We have a situation where are no relationships are defined at the database level. i.e no foreign keys constraints have established at the Database. The application is still at the Development Stage. Everything is controlled at the application level. I as the DBA appose this design for Data security and also cannot reverse engineer from the tables into Designer. Can you please share you pros / Cons. Thanks __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ramani akhil INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: FK Constraints
the DBA is not responsible for the data?? Could you PLEASE come to my office and explain that (I'll bring the heavy club) to my developers, users and management? They seem to believe that my primary function is to dig information out of the database for them. Backup and recovery? Capacity planning? Why on earth would I want to do THAT? >From: [EMAIL PROTECTED] >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: FK Constraints >Date: Mon, 19 Mar 2001 13:20:34 -0800 > > >I'll have to disagree with this. I've seen to many >programs break when the constraints were enabled. > >Developers generally don't have a clue how to maintain >integrity in the application data. This is not to impugn >all developers, just about 90% of them. > >The database knows how to enforce integrity, developers don't. > >As a DBA I insist on RI in the database. > >When there is no RI in the database, DBA's are constantly >being called for 'database problems' when the problem is >actually in the data, which is not our responsibility. > > >The DBA spends much time proving the source of the problem >since the developer is unable to. > >More fodder for duhveloper.com. > >Jared > >On Mon, 19 Mar 2001, Holman, Rodney wrote: > > > You are running into a primary point of contention between many DBA's >and > > developers. Dev's don't like the use of PK/FK relationships within the > > database because it is not always portable across multiple RDBMS's. By > > making the app handle all the referential integrity issues they can say > > "Yes, our system runs on Oracle, Sybase, Informix, MS SQL Server, or >even > > Access " All they need the RDBMS to do is store tables and indexes >then. > > Many third party apps use this approach. The problem is when their code >is > > correct and perfect, they are right. You don't NEED FK's. However I >have > > yet to run across any super human developer that codes everything >perfectly. > > What happens... you get duplicate key values and child tables filled >with > > orphans because somewhere in the code a restriction was missed. By > > assigning these at the DB level you can forget about having to maintain >it > > in the app. The RDBMS does it for you. Push for RDBMS level control of > > this. It will save you many headaches later over data corruption. > > > > Rodd Holman > > > > -Original Message- > > Sent: Monday, March 19, 2001 12:05 PM > > To: Multiple recipients of list ORACLE-L > > > > > > Hi all: > > > > We have a situation where are no relationships are > > defined at the database level. i.e no foreign keys > > constraints have established at the Database. The > > application is still at the Development Stage. > > > > Everything is controlled at the application level. > > > > I as the DBA appose this design for Data security and > > also cannot reverse engineer from the tables into > > Designer. > > > > Can you please share you pros / Cons. > > > > Thanks > > > > > > > > > > > > __ > > Do You Yahoo!? > > Get email at your own domain with Yahoo! Mail. > > http://personal.mail.yahoo.com/ > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: ramani akhil > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing Lists > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Holman, Rodney > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California-- Public Internet access / Mailing Lists > > > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED]
Re: FK Constraints
Jared and Jaques, I agree with you completely. RI should always be enforced at the DB level. I was just sharing my experience with a couple of 3rd party sotfware vendors that wouldn't allow me to put the constraints in the DB. In fact one of them had a lookup table hardcoded into the app interface. If you tried to enforce RI in the DB it would fail looking for parents on one of the child tables to this hardcoded table. (To stop any disparging of my credibility I had NO say in the purchasing of this RI nightmare. I was "gifted" with the "opportunity to excel" when the !@#$%& that stuck us with the app quit the company.) Their sole reason for the approach (valid or not) was db portability. Sadly small software shops crop up to fill niche needs and do not necessarily employ people who understand the inner workings of RDBMS's. They know how to make their cute forms and flashy reports using some wizbang dev tool and totally ignore the fact that a real foundation is need for proper functionality. On Monday 19 March 2001 15:21, you wrote: > I'll have to disagree with this. I've seen to many > programs break when the constraints were enabled. > > Developers generally don't have a clue how to maintain > integrity in the application data. This is not to impugn > all developers, just about 90% of them. > > The database knows how to enforce integrity, developers don't. > > As a DBA I insist on RI in the database. > > When there is no RI in the database, DBA's are constantly > being called for 'database problems' when the problem is > actually in the data, which is not our responsibility. > > > The DBA spends much time proving the source of the problem > since the developer is unable to. > > More fodder for duhveloper.com. > > Jared -- Rodd Holman Oracle DBA (605) 988-1373 [EMAIL PROTECTED] Comments made are my own opinions and views. They do not represent views, policies, or procedures of LodgeNet Entertainment Corporation --- -- Rodd Holman Oracle DBA (605) 988-1373 [EMAIL PROTECTED] Comments made are my own opinions and views. They do not represent views, policies, or procedures of LodgeNet Entertainment Corporation -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodd Holman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: FK Constraints
I'll have to disagree with this. I've seen to many programs break when the constraints were enabled. Developers generally don't have a clue how to maintain integrity in the application data. This is not to impugn all developers, just about 90% of them. The database knows how to enforce integrity, developers don't. As a DBA I insist on RI in the database. When there is no RI in the database, DBA's are constantly being called for 'database problems' when the problem is actually in the data, which is not our responsibility. The DBA spends much time proving the source of the problem since the developer is unable to. More fodder for duhveloper.com. Jared On Mon, 19 Mar 2001, Holman, Rodney wrote: > You are running into a primary point of contention between many DBA's and > developers. Dev's don't like the use of PK/FK relationships within the > database because it is not always portable across multiple RDBMS's. By > making the app handle all the referential integrity issues they can say > "Yes, our system runs on Oracle, Sybase, Informix, MS SQL Server, or even > Access " All they need the RDBMS to do is store tables and indexes then. > Many third party apps use this approach. The problem is when their code is > correct and perfect, they are right. You don't NEED FK's. However I have > yet to run across any super human developer that codes everything perfectly. > What happens... you get duplicate key values and child tables filled with > orphans because somewhere in the code a restriction was missed. By > assigning these at the DB level you can forget about having to maintain it > in the app. The RDBMS does it for you. Push for RDBMS level control of > this. It will save you many headaches later over data corruption. > > Rodd Holman > > -Original Message- > Sent: Monday, March 19, 2001 12:05 PM > To: Multiple recipients of list ORACLE-L > > > Hi all: > > We have a situation where are no relationships are > defined at the database level. i.e no foreign keys > constraints have established at the Database. The > application is still at the Development Stage. > > Everything is controlled at the application level. > > I as the DBA appose this design for Data security and > also cannot reverse engineer from the tables into > Designer. > > Can you please share you pros / Cons. > > Thanks > > > > > > __ > Do You Yahoo!? > Get email at your own domain with Yahoo! Mail. > http://personal.mail.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: ramani akhil > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Holman, Rodney > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: FK Constraints
You are running into a primary point of contention between many DBA's and developers. Dev's don't like the use of PK/FK relationships within the database because it is not always portable across multiple RDBMS's. By making the app handle all the referential integrity issues they can say "Yes, our system runs on Oracle, Sybase, Informix, MS SQL Server, or even Access " All they need the RDBMS to do is store tables and indexes then. Many third party apps use this approach. The problem is when their code is correct and perfect, they are right. You don't NEED FK's. However I have yet to run across any super human developer that codes everything perfectly. What happens... you get duplicate key values and child tables filled with orphans because somewhere in the code a restriction was missed. By assigning these at the DB level you can forget about having to maintain it in the app. The RDBMS does it for you. Push for RDBMS level control of this. It will save you many headaches later over data corruption. Rodd Holman -Original Message- Sent: Monday, March 19, 2001 12:05 PM To: Multiple recipients of list ORACLE-L Hi all: We have a situation where are no relationships are defined at the database level. i.e no foreign keys constraints have established at the Database. The application is still at the Development Stage. Everything is controlled at the application level. I as the DBA appose this design for Data security and also cannot reverse engineer from the tables into Designer. Can you please share you pros / Cons. Thanks __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ramani akhil INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Holman, Rodney INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: FK Constraints
Title: RE: FK Constraints > -Original Message- > From: Holman, Rodney [mailto:[EMAIL PROTECTED]] > > You are running into a primary point of contention between > many DBA's and > developers. Dev's don't like the use of PK/FK relationships > within the > database because it is not always portable across multiple > RDBMS's. By > making the app handle all the referential integrity issues > they can say > "Yes, our system runs on Oracle, Sybase, Informix, MS SQL > Server, or even > Access " All they need the RDBMS to do is store tables > and indexes then. > Many third party apps use this approach. The problem is when > their code is > correct and perfect, they are right. You don't NEED FK's. I have heard this argument before and I don't see that it has any justification in fact. If a developper came to me and said: "My application is going to be used against several databases, so a) Constraints may not always be supported in the underlying RDBMS, and b) Anyway my code does things right", I would answer a) Why not use constraints where they are supported, and b) If your code does things right, why do you care if the constraints are there? If your code isn't right, then constraints will help you find the bugs. -- any ignorant comments made are the sole responsibility of J. R. Kilchoer and should not reflect adversely upon my employer. Jacques R. Kilchoer (949) 754-8816 Quest Software, Inc. 8001 Irvine Center Drive Irvine, California 92618 U.S.A. http://www.quest.com
RE: FK Constraints
Title: RE: FK Constraints > -Original Message- > From: ramani akhil [mailto:[EMAIL PROTECTED]] > > We have a situation where are no relationships are > defined at the database level. i.e no foreign keys > constraints have established at the Database. The > application is still at the Development Stage. > > Everything is controlled at the application level. > > I as the DBA appose this design for Data security and > also cannot reverse engineer from the tables into > Designer. > > Can you please share you pros / Cons. If relationships are supposed to exist between tables in the database, then they should be enforced by integrity constraints. I can think of a few reasons for not having the constraints: a) If there are bugs in the application that prevent the relationships from being enforced. Of course the best situation would be to fix the application and keep the constraints. b) If the relationships exist logically but the application inserts rows in the tables in the "wrong order", i.e. in the child table first and then the parent table. Again the application should probably be re-written if time allows. c) Performance reasons? I.e. without the extra constraints inserts in a table may happen slightly faster? I don't think I've ever heard of a real-life situation where the performance gain would be important enough to forego the data integrity ensured by constraints, but I suppose it could happen. My short opinion: I see a lot more cons than pros. -- any ignorant comments made are the sole responsibility of J. R. Kilchoer and should not reflect adversely upon my employer. Jacques R. Kilchoer (949) 754-8816 Quest Software, Inc. 8001 Irvine Center Drive Irvine, California 92618 U.S.A. http://www.quest.com
RE: FK Constraints
issue is more of data integrity than security if this is the only apps working against that database and no future development is going to occur then no problemo. just two of the cons cons cons 1. maintenance would be difficult. as one wld require to parse thru the code to find out the relationships. 2. relationships that are considered as business facts shld always be implemented as part of the database as it shld centralized. it wld apply for apps against that database. > -Original Message- > From: ramani akhil [mailto:[EMAIL PROTECTED]] > Sent: Monday, March 19, 2001 12:05 PM > To: Multiple recipients of list ORACLE-L > Subject: FK Constraints > > > Hi all: > > We have a situation where are no relationships are > defined at the database level. i.e no foreign keys > constraints have established at the Database. The > application is still at the Development Stage. > > Everything is controlled at the application level. > > I as the DBA appose this design for Data security and > also cannot reverse engineer from the tables into > Designer. > > Can you please share you pros / Cons. > > Thanks > > > > > > __ > Do You Yahoo!? > Get email at your own domain with Yahoo! Mail. > http://personal.mail.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: ramani akhil > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandar Ghosalkar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: FK Constraints
A disaster waiting to happen; it's not if one will happen, it's when. Applications, no matter how well written, will, sooner or later, lose transactional control because of design flaws, new front ends that don't enforce ACID transactions (or interfaces from other systems) or other acts of development or network failure. DON'T DO IT! I've seen too many projects fail, and spent too many hours trying the impossible: cleaning up integrity violations after the fact. If you want real life examples, I'll be happy to supply off-line (protecting the not-so-innocent) Ed Maurer Sr. DBA Acquirex > -Original Message- > From: ramani akhil [mailto:[EMAIL PROTECTED]] > Sent: Monday, March 19, 2001 10:05 AM > To: Multiple recipients of list ORACLE-L > Subject: FK Constraints > > > Hi all: > > We have a situation where are no relationships are > defined at the database level. i.e no foreign keys > constraints have established at the Database. The > application is still at the Development Stage. > > Everything is controlled at the application level. > > I as the DBA appose this design for Data security and > also cannot reverse engineer from the tables into > Designer. > > Can you please share you pros / Cons. > > Thanks > > > > > > __ > Do You Yahoo!? > Get email at your own domain with Yahoo! Mail. > http://personal.mail.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: ramani akhil > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ed Maurer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
FK Constraints
Hi all: We have a situation where are no relationships are defined at the database level. i.e no foreign keys constraints have established at the Database. The application is still at the Development Stage. Everything is controlled at the application level. I as the DBA appose this design for Data security and also cannot reverse engineer from the tables into Designer. Can you please share you pros / Cons. Thanks __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ramani akhil INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
