Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Database Design nightmares...

Database Design nightmares...

From: Norman Dunbar <>
Date: Thu, 18 Jul 2002 16:34:52 +0100
Message-ID: <>

How about :

A system where there are three or more schemas holding different parts of the same application.
User 1 has procs that need views and tables from user 3 User 3's views refer to views and tables in User 2. User 2's views refer back to views and tables in User 1 and User 3 etc etc. (This isn't circular in any way is it ?)

Now, when the system is up and running, every import you do invalidates heaps of objects because of the circular references. There is no simple order or way to get everything in without invalid objects. Then the fun really begins - compile all invalid objects in User 1 then in 2 then in 3 then back to 1 to catch up etc etc.

And the system has :

very few primary keys,
referential integrity is done in code, not in the DBMS, millions of indexes,
package headers in the same source file as package code - leading to invalidations all over the place any time a change is made, leading once more to the 'magic roundabout' chase around the users to un-invalidate everything .....

Why is it designed like this ?
I asked and was told "it was originally a Cobol system with flat files and we migrated it to Oracle exactly as it was". Well, when I did Cobol last, I used Indexed files, not serial files, and I had unique keys on them - why the hell didn't this system.

(Oh, and if you followed a previous thread, all users have CREATE ANY PROCEDURE and EXECUTE ANY PROCEDURE privs granted).

And it can be used with the CBO - yet !
And you can't have more than one set of users in a database because the username(s) are hard coded into the code - AArrrgh !

This system is the one I loath with a capital 'F' :o)


Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
Tel: 0113 289 6265
Fax: 0113 289 3146


-----Original Message-----
From: (NetComrade) []
Posted At: Wednesday, July 17, 2002 3:44 PM Posted To: server
Conversation: Database Design nightmares... Subject: Database Design nightmares...

It is assumed that (bad) Database Design could be the worst bottleneck in database performace. Curious in kinds of 'really bad' designs people have encountered and what hardships they had to go though to modify the schema or work around it to accomodate a customer.

We use Oracle on Solaris 2.7 boxes remove NSPAM to email Received on Thu Jul 18 2002 - 10:34:52 CDT

Original text of this message