Re: Database Design

"Nan" <nandagopalj_at_hotmail.com> wrote in message news:8193246.0401021126.3affe09f_at_posting.google.com...
> Hello-
>
> I recently came across an application that has 400+ tables in an
> Oracle database. The tables do not have referential integrity
> contraints between them. For example a record in a child table can
> exist without a corresponding row in the parent table. Or a data
> column can contain a value that is not present in the master table
> containing all different values.
>
> What are the typical reasons for such a database design without
> referential constraints ?
>
> My guess is that by doing away with the referential contraints, the
> application performs faster. Further reasoning is that using APIs to
> create/delete/update data would keep the integrity of the data without
> a need for the database referential constraints.
>
> I cannot seem to agree with the approach or its reasoning. Without the
> database referential contraints, the quality of data goes down the
> drain over a period of time - mostly due to data fixes needed outside
> of APIs via scripts, etc.
>
> I would like to know your perspective on this aspect..pros/cons and
> any material that discusses this particular aspect of the database
> design is much appreciated.
>
> Thanks,
> Nan.

you are spot on in your concerns, you wont find anyone here who likes having the app do the checking - its what the database is there for.

The common reason for doing it in the app is to have a database independant product. But that is no excuse for anything as you WILL lose yur data someday.

Go to http://asktom.oracle.com and search for this stuff, find some 'interesting' comments Received on Fri Jan 02 2004 - 14:02:02 CST