Re: Database Design

Comments embedded

On 2 Jan 2004 11:26:01 -0800, nandagopalj_at_hotmail.com (Nan) wrote:

>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 ?

1 They don't know how to design a database model with constraints 2 'Our product runs against Sqlserver, Oracle, Sybase, DB2 or whatever. Due to differences between database platforms we can't implement constraints in the database. By doing so, it doesn't matter on which database you run our product'
3 'Disk is cheap. Why delete records when you can simply flag them as deleted, and our app will suppress them everywhere' (I actually heard a 3rd party vendor consultant saying this to me when I questioned the lack of integrity
>
>My guess is that by doing away with the referential contraints, the
>application performs faster.

This is not necessarily true. Usually these 3rd party vendors don't know anything about sql tuning, and they implement referential integrity checks by means of explicit sql. This is usually much slower than having Oracle check integrity (A test a few years ago showed that constraints where a factor 8 faster compared to triggers)

 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.

Yes, but see above: this is explicit sql. Also this sql is usually generated on the fly, without using bind variables, the perfect means to make an application non-scalable.
Also in many cases all foreign keys are non-indexed, forcing Oracle to conduct full table scans for almost every statement.

>
>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.

It keeps me employed.
It is useless to try to talk sense into the 3rd party vendor representatives, because usually they only know a bit of SQL and a bit of SQLserver and 0 about Oracle. And as you might know: even Oracle Applications doesn't have RI in the database.

Just FYI this topic has been discussed extensively a few months ago, so Google might help you. If I recall correctly the thread even included participants defending the superiority of checking RI in an application server. Needless to say those people have been proven wrong all the times.

>
>Thanks,
>Nan.

--
Sybrand Bakker, Senior Oracle DBA