Re: Philosopical debate about constraints

David Since this is a philosophical question, there could be lots of answers and probably all of them would be correct for the environment its running in. However, this is how I <i>like</i> to do it, whenever I have the good fortune to come in to a project at a stage when such decisions are made: 1) Use constraints for RI. As many people here have pointed out, this prevents data corruptions later on. 2) Use stored procedures or an (ESQL/C or 4GL) application layer to define operations on business objects, which could be one (or frequently more) database tables. Test this layer in isolation. Typically operations are of the form get_ and set_ much like JavaBeans objects. Although it requires a lot of thought to design these operations so that they are flexible enough. 3) Write the application. The "application" programmer would use the functions that have been defined in step 2 to access the database. Test layer 3 and layer 2 together. This approach may seem utopian, and frequently is, but implemented correctly, this makes the application easier to maintain from the point of view of the application's programmer. Even when you are adding or modifying tables, you need to make changes to layer (2) only since the business objects behave the same way, regardless of the implementation. Its usually easier (read possible) to implement this in the design stage of the application, rather than retrofitting this to an existing application. Just my $0.02 Sujit mars1972@my-deja.com on 11/09/99 11:14:33 AM Please respond to mars1972@my-deja.com To: informix-list@iiug.org cc: (bcc: Sujit Pal) Subject: Re: Philosopical debate about constraints In article <809fj2$t2t$1@news.xmission.com>, "Obnoxio The Clown" <obnoxio@hotmail.com> wrote: > > From: "David Henry" <henryd@net-gong.com> > > > >Assuming: > >1. All application access to my database is via stored procedures. > >2. My stored procedures can be relied upon to do sanity checks regarding > >relationships between tables > > > >then is it really necessary to define constraints? Isn't that just an > >unnecessary overhead and just duplicating the work of my sanity checks? > > > >Your thoughts would be appreciated. > > I think your assumptions are "interesting" and probably practically > impossible to enforce. :-) > > ______________________________________________________ > Get Your Private, Free Email at http://www.hotmail.com > I have worked on a system where all users accessed data solely through stored procedures. These procedures had some of the sanity checks you're talking about. However: 1) There were also several programs that ran 'under the covers' that did not use stored procedures because they were long batch jobs. 2) programmers, whether ESQL/C, 4GL, SQL, or Stored procedure programmers (etc...) make mistakes. 3) If a constraint changes, (field 1 used to be required and had to match field 4 in table x, but now it isn't) it's much easier and more reliable to change a single constraint that every stored procedure. 4) does 'on delete cascade' fall under the constraint category? 5) I would think it's more efficient to have the engine do sanity checks than the sp. -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.