Re: Database Design nightmares...

Norman Dunbar wrote:
>
> 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)
>
> regards,
> Norman.
>
> -------------------------------------
> Norman Dunbar
> Database/Unix administrator
> Lynx Financial Systems Ltd.
> mailto:Norman.Dunbar_at_LFS.co.uk
> Tel: 0113 289 6265
> Fax: 0113 289 3146
> URL: http://www.Lynx-FS.com
> -------------------------------------
>
> -----Original Message-----
> From: andreyNSPAM_at_bookexchange.net (NetComrade)
> [mailto:andreyNSPAM_at_bookexchange.net]
> 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.
>
> Thanx.
> .......
> We use Oracle 8.1.7.3 on Solaris 2.7 boxes
> remove NSPAM to email

I used a product called Walker (interestingly I once heard that it was written by a guy named Jeff Walker who allegedly left and had a hand in the design of Oracle Apps - anyone confirm or deny? - the products certainly share a common feel) but I digress..

... anyway, Walker had started as a vsam system which had been ported to relational databases. Thus the vast majority of the tables looked like

SQL> desc A_WALKER_TABLE

  COL DATATYPE

  table_name  varchar2(100)
  key         varchar2(1000)
  data        varchar2(1000)

Yup - lots of "logical" Walker tables inside a single physical table. The true columns for the keys and the data could be located by carefully examining the COBOL copybooks....Magic!

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."