Re: [PERFORM] FOREIGN KEYS vs PERFORMANCE
Thanks for all responses! I agree with most of you, and say that the RI is best maintened by Database ! Performance must be improved in other ways (indexes, hardware, etc)! - Original Message - From: "Jim C. Nasby" <[EMAIL PROTECTED]> To: "Craig A. James" <[EMAIL PROTECTED]> Cc: "PFC" <[EMAIL PROTECTED]>; "Michael Glaesemann" <[EMAIL PROTECTED]>; "Rodrigo Sakai" <[EMAIL PROTECTED]>; Sent: Wednesday, April 12, 2006 5:59 PM Subject: Re: [PERFORM] FOREIGN KEYS vs PERFORMANCE > On Wed, Apr 12, 2006 at 10:36:28AM -0700, Craig A. James wrote: > > Jim C. Nasby wrote: > > >>1. You have only one application that modifies the data. (Otherwise, you > > >>have to duplicate the rules across many applications, leading to a > > >>code-maintenance nightmare). > > > > > >You forgot something: > > > > > >1a: You know that there will never, ever, ever, ever, be any other > > >application that wants to talk to the database. > > > > > >I know tons of people that get burned because they go with something > > >that's "good enough for now", and then regret that decision for years to > > >come. > > > > No, I don't agree with this. Too many people waste time designing for > > "what if..." scenarios that never happen. You don't want to be dumb and > > design something that locks out a foreseeable and likely future need, but > > referential integrity doesn't meet this criterion. There's nothing to keep > > you from changing from app-managed to database-managed referential > > integrity if your needs change. > > In this case your argument makes no sense, because you will spend far > more time re-creating RI capability inside an application than if you > just use what the database offers natively. > > It's certainly true that you don't want to over-engineer for no reason, > but many times choices are made to save a very small amount of time or > hassle up-front, and those choices become extremely painful later. > -- > Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] > Pervasive Software http://pervasive.comwork: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly > ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] FOREIGN KEYS vs PERFORMANCE
Thanks for all help!! But my problem is with performance, I agree with all of you, the RI must be maintained by the database, because a bunch of reasons that everyone knows! But, I'm dealing with a very huge database that servers more than 200 clientes at the same time, and because of it, each manipulation (delete, insert, update, select) on the database have a poor performance. So, if we deal with RI in each client station, we take this work off the database! The application is an ERP developed with DELPHI + (postgresql or oracle or sql server)!! Thanks again!! - Original Message - From: "Markus Schaber" <[EMAIL PROTECTED]> To: Cc: "Rodrigo Sakai" <[EMAIL PROTECTED]> Sent: Wednesday, April 12, 2006 10:18 AM Subject: Re: [PERFORM] FOREIGN KEYS vs PERFORMANCE > Hi, Michael, > Hi, Rodrigo, > > Michael Glaesemann wrote: > > > If I had to choose between one or the other, I'd leave all referential > > integrity in the database and deal with the errors thrown when > > referential integrity is violated in the application. PostgreSQL is > > designed to handle these kinds of issues. Anything you code in your > > application is more likely to contain bugs or miss corner cases that > > would allow referential integrity to be violated. PostgreSQL has been > > pounded on for years by a great many users and developers, making the > > likelihood of bugs still remaining much smaller. > > I strictly agree with Michael here. > > > Of course, you can add some referential integrity checks in your > > application code, but those should be in addition to your database- > > level checks. > > Agree. It does make sense to have reference checks in the UI or > application level for the sake of better error handling, but the > database should be the mandatory judge. > > There's another advantage of database based checking: Should there ever > be the need of a different application working on the same database (e. > G. an "expert level UI", or some connector that connects / synchronizes > to another software, or a data import tool), database based constraints > cannot be broken opposed to application based ones. > > HTH, > Markus > -- > Markus Schaber | Logical Tracking&Tracing International AG > Dipl. Inf. | Software Development GIS > > Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] FOREIGN KEYS vs PERFORMANCE
Hi, I think this is an old question, but I want to know if it really is well worth to not create some foreign keys an deal with the referential integrity at application-level? Specifically, the system we are developing is a server/cliente architecture that the server is the database and the fat client is an application developed in DELPHI!!! Thanks in advance!!
