RE: Database Foreign key constraints vs Application Maintained Constraints

Yes - I'm not totally sure about the idea of seperate database schema per 'sub-systems' either.

This idea comes from the fact that the company has a vision of agile teams woking independently of other teams.

Each team is responsible for its own sub-system (or 'vertical'). These sub-systems will have their own release cycle.

There is a perception that multiple schemas would allow these teams greater freedom to make changes at database level.

But the more I think about it, the less I agree ...

br
peter

> From: gmei_at_prospectiv.com
> To: bellpk_at_hotmail.com; oracle-l_at_freelists.org
> Subject: Re: Database Foreign key constraints vs Application Maintained Constraints
> Date: Mon, 20 Apr 2009 16:29:19 -0400
>
> No FK in db is not a good idea. It is just matter of time that some orphan
> records will appear in db and you do not know if they are junk data (which
> should not be there, but inserted accidently), or they are valid data but
> with parents missing. At that point, developers will not admit any
> "mistakes" on their part. DBA has to "fix" the issue. Talkng about first
> hand experience.
>
> I don't know your reason(s) to 'break up' the application, but it seems to
> me that these sub-systems could stay in same schema, as long as there is no
> db object name conflict. Accessing different schema objects require all
> kinds privs granted to original schema owner. It can be done with 5-10
> different sub-users but why to create this "headache"?
>
> Guang Mei
> www.eversave.com
>
> ----- Original Message -----
> From: "peter bell" <bellpk_at_hotmail.com>
> To: "l oracles" <oracle-l_at_freelists.org>
> Sent: Monday, April 20, 2009 3:53 PM
> Subject: Database Foreign key constraints vs Application Maintained
> Constraints
>
>
> >
> >
> > I'm looking for thoughts / feedback / experiences of :
> >
> > - applications that leave enforcement of foreign key relationships to the
> > application (or simply assume that those relationships will not be
> > violated) in an OLTP system
> >
> > - any considerations when creating foreign key constraints between tables
> > in different schemas.
> >
> > The background to this request follows below for those interested.
> >
> > thanks,
> > peter
> >
> >
> > Background :
> >
> > Our company is redesigning its J2EE OLTP application. One objective is to
> > 'break up' the application into smaller loosely coupled 'sub-systems'.
> >
> > The proposal is that the 'break up' extend to database level with each
> > sub-system having a seperate database schema.
> >
> > Some tables (such as the Customer table) will naturally have one-to-many
> > relationships with tables in many or all of the sub-system schemas. I'll
> > refer to the schema containing these common objects as the 'master'
> > schema.
> >
> > All master and sub-system schemas are in the same database instance.
> >
> > I had assumed that the relationships between master and sub-system schemas
> > would be maintained with cross-schema foreign key constraints. However,
> > the development team have proposed we 'relax' this and not have any
> > foreign key constraints between master and sub-system schemas.
> >
> > Some of the reasons put forward for this are :
> >
> > - allows teams responsible for each sub-system to develop and refactor
> > their schema freely / independently of others (without the need for the
> > master schema)
> > - reduces the potential for sub-systems to block / lock records in the
> > master schema (and presumably vice versa)
> > - since master and sub-systems always interact via some middle tier java
> > api using globally distributed (XA) transactions and because we never
> > delete records in the master schema, data integrity violations should not
> > occur
> >
> > My initial reactions are :
> >
> > - Some blocking / locking is necessary if you want data integrity
> > - I dont see how globally distributed transactions would necessarily
> > reduce that or FK's increase it. Is it true to say that a foreign key
> > constraint still allows
> >
> > - deletion of the child table records without blocking / locking the
> > master
> > - update of the child table without blocking / locking the master
> > - insert into a child table without blocking / locking the master
> > - update any column in the master EXCEPT the primary key without blocking
> > / locking the child table rows
> >
> > ?
> >
> > Obviously, deletion of the master record could block or be blocked by the
> > sub-system. Likewise, updating the primary key of the master table could
> > block or be blocked. But we should never do either of these.
> >
> > On the face of it, I can only see up sides to using foreign keys (data
> > integrity with a simple declarative foreign key constraint). Without them,
> > transactions inside a sub-system / or master schema could cause
> > 'constraint' violations - there would be nothing to stop a sub-system
> > schema adding a record with a non-existent customer_id (however, the fact
> > that deletions from the master are outlawed may mean this is unlikely).
> >
> > It looks like we will have a handful of sub-system schemas (between 5 and
> > 10).
> >
> > _________________________________________________________________
> > Share your photos with Windows Live Photos – Free.
> > http://clk.atdmt.com/UKM/go/134665338/direct/01/--
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l