Re: Database Design

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

IF the final physical implementation resembles a properly normalized design, then integrity should be declared into the database using the RI capability. These days it generally tests out as fast, and a lot cleaner in the long run, than leaving the RI in the application code.

IF the implementation uses a 'logical' rather than 'physical' normalization, then the RI generally must end up in the application code. There are a number of designers/developers who separate the logical from the physical and use heavily denormalized dictionaries, believing they can do the job better in their own code.

Some observations from recent conversations:

IF you are dealing with a JAVA J2EE, note that some Java bit-heads believe everything should be done in the middle tier and the database should be very simple - i.e. with no RI, no type or data checking, etc. Some would even prefer the database to consist of 1 table with 2 columns - a key, and a dynamic "everything else" column. If nothing else, this supports the need for another 1500 classes & 4500 methods to learn and that could be considered better/easier than learning basic SQL.

Note that some developers have not been trained in basic database design and are not aware that a database can even provide RI. In my experience, this is especially true of Visual Basic or C# developers I've met, possibly because many of them are self taught.

I know of at least one developer who insists on RI in the app code simply to keep his contract going longer. As new parts of app are created, he can cut & paste 'new code'.

Sadly, in some situations the developers and DBAs have an US vs THEM attitude: possibly due to arrogance; fear of job loss; lack of knowledge; or politics. Instead of talking, there is a great divide, with each side [justifiably??] blaming the other. Received on Fri Jan 02 2004 - 15:29:38 CST