Good points. After I sent the reply, I thought of other ways to corrupt the
database besides application errors. Some older, less reliable application
might write to the same database. Or a stored procedure or a one-time
maintenance script in the future might have a bug, and usually the damage
won't be noticed until later when it's not easy to correct.
----- Original Message -----
From: "Cris Daniluk" <cris.danilu..laraview.com>
To: <cayenne-use..bjectstyle.org>
Sent: Friday, March 18, 2005 3:38 PM
Subject: RE: Foreign keys
> I'd have to agree. To me, not using foreign keys is only for times when
you
> really know what you're doing and have a damn fine reason. I've come into
> more than one project with a constraint-free data model and even though
the
> application made it "impossible" for a violation to happen, there were
> mysteriously tons of problems.
>
> The best approach is probably to sit down with your dba and talk about why
> they want to do this. Usually, its because they want to archive historical
> data periodically. There's almost always an alternative to this approach,
if
> you're willing to shoulder some of the burden on the development team.
>
> Cris
>
> > -----Original Message-----
> > From: Andrus Adamchik [mailto:andru..bjectstyle.org]
> > Sent: Friday, March 18, 2005 3:12 PM
> > To: cayenne-use..bjectstyle.org
> > Subject: Re: Foreign keys
> >
> > Of course technically this can be done - you can use Cayenne
> > with or without DB constraints. First Cayenne doesn't care -
> > it will work the same way. Second, it will try to do its best
> > to preserve integrity rules defined as relationships.
> >
> > This becomes a design philosophy issue (or maybe
> > s/philosophy/religion/).
> > To me constraints is one last thing that separates universal
> > order from universal chaos. Turn off constraints, and your
> > database becomes an overblown text file - any application can
> > turn it into garbage in no time.
> >
> > > i saw in ur tutorial and tutoriadb file in that also u dont
> > have any
> > > foreign key relationship for ARTIST, GALLERY and PAINTING tables.
> >
> > That's because tutorial targets MySQL 3.* that does not
> > support constraints. There is no deeper wisdom in that example.
> >
> > Just my $.02 :-)
> >
> > Andrus
> >
> >
> >
> > > I'm not a DBA type, but I've been down a similar learning curve.
> > >
> > > Yes, it is possible to have relationships maintained only
> > in the app.
> > > In addition (optionally), the database can impose foreign-key
> > > constraints. Using both approaches would give you a bit more
> > > protection against corrupted data, in case there was a
> > subtle bug in
> > > the application... if the app tried to commit an invalid
> > object graph,
> > > the database would catch it and cause an exception to be thrown.
> > >
> > > I believe it is fairly common practice to maintain
> > relationships only
> > > in the application, as your DBA is asking you to do. (In fact some
> > > databases might not support foreign-key constraints.) It makes
> > > database maintenance tasks somewhat easier. Especially when using
> > > Cayenne, which will do a good job of validating the object
> > graph before it's committed.
> > > Your application will have an easier job of handling Cayenne's
> > > validation errors than database-level exceptions, so this approach
> > > would probably be easier for you as well.
> > >
> > > If the application is successful (if it lives a long time and the
> > > database grows large), you can always choose to go back
> > later and add
> > > foreign-key constraints to the database for that extra bit of
> > > protection, without changing your application.
> > >
> > >
> > >
> > > ----- Original Message -----
> > > From: "Sami Mohammed" <SMOHAMME..ndependenthealth.com>
> > > To: <cayenne-use..bjectstyle.org>
> > > Sent: Friday, March 18, 2005 11:28 AM
> > > Subject: Foreign keys
> > >
> > >
> > >
> > > My tables structure are given below:
> > >
> > > 1. User_table
> > > user_key number primary key,
> > > user_id varchar,
> > > F&Lname varchar,
> > > psswd varchar,
> > > lastLogin date,
> > > ActiveFlag char(1)
> > > sec_master_key number referening to
> > > security_master_table(sec_master_key ) or foreign key
> > >
> > > 2.sec_master_Table Table:
> > > sec_master_key number primarykey,
> > > description varchar,
> > > err_role_key number foreign key refering to table err_role_table,
> > > cw_role key number foreign key refering to table cw_role_table,
> > > last_chng_date date.
> > >
> > > 3.err_role_table
> > > err_role_key number primary key,
> > > err_role_id varchar,
> > > err_role_description,
> > > last_chng_date date
> > >
> > > 4. cw_role_table
> > > cwr_role_key number primary key,
> > > cw_role_id varchar,
> > > errcw
> > > last_chng_date date.
> > >
> > > i have question:
> > >
> > > in the above table structure if we remove the foreign keys and
> > > maintain the relationship using in the application, is it
> > possible. My
> > > db analyst ask me to maintain in webappliction
> > >
> > > i saw in ur tutorial and tutoriadb file in that also u
> > dont have any
> > > foreign key relationship for ARTIST, GALLERY and PAINTING tables.
> > > But in the datamap.map file, i saw relationship. was it
> > done manually
> > > or what. I am using the techonolgy for quite some time, but
> > still i am
> > > not strong, may be i am asking some silly question. sorry for that.
> > >
> > > see below for db-relationship
> > >
> > > <db-relationship name="paintingArray" source="ARTIST"
> > target="PAINTING"
> > > toMany="true">
> > > <db-attribute-pair source="ARTIST_ID" target="ARTIST_ID"/>
> > > </db-relationship> <db-relationship name="paintingArray"
> > > source="GALLERY" target="PAINTING"
> > > toMany="true">
> > > <db-attribute-pair source="GALLERY_ID" target="GALLERY_ID"/>
> > > </db-relationship> <db-relationship name="toArtist"
> > source="PAINTING"
> > > target="ARTIST"
> > > toMany="false">
> > > <db-attribute-pair source="ARTIST_ID" target="ARTIST_ID"/>
> > > </db-relationship> <db-relationship name="toGallery"
> > source="PAINTING"
> > > target="GALLERY"
> > > toMany="false">
> > > <db-attribute-pair source="GALLERY_ID" target="GALLERY_ID"/>
> > > </db-relationship> <obj-relationship name="paintingArray"
> > > source="Artist" target="Painting"
> > > db-relationship-path="paintingArray"/>
> > > <obj-relationship name="paintingArray" source="Gallery"
> > > target="Painting" db-relationship-path="paintingArray"/>
> > > <obj-relationship name="toArtist" source="Painting" target="Artist"
> > > db-relationship-path="toArtist"/>
> > > <obj-relationship name="toGallery" source="Painting"
> > target="Gallery"
> > > db-relationship-path="toGallery"/>
> >
> >
> >
> >
>
This archive was generated by hypermail 2.0.0 : Fri Mar 18 2005 - 16:15:06 EST