Re: Database Design

"Nan" <nandagopalj_at_hotmail.com> wrote in message news:8193246.0401021126.3affe09f_at_posting.google.com...
>
> What are the typical reasons for such a database design without
> referential constraints ?

1- Sheer stupidity.
2- Sheer ignorance.
3- Porting (on the cheap) from an environment

   that doesn't support them.
4- J2EE "OO" design a-la Scott Amler. Synonym for 1 and 2 above.

> My guess is that by doing away with the referential contraints, the
> application performs faster.

A good guess. That argument is often invoked. It is in 100% of the cases incorrect.

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

How can that be achieved? What insurance does ANYONE have that change access to the db will ALWAYS go through the API?
> I cannot seem to agree with the approach or its reasoning.

You're not alone.

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

Absolutely! What if someone comes in and develops a second API for another application? Which they'll most certainly do if the first one is in Java and proprietary of an application developer?

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

You got it. Material? Well, just about ANY text on database design of the last 30 years will mention why it is important to keep RI in the database. I said "database design", not "OO design". Not sure if the latter is not a synonym for sheer ignorance... Do a search on google for Fabian Pascal and go to his website. Read most of the materials. Then follow on to Date, Codd, etc.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam