Re: Can Sybase and Oracle RDBs be designed in non first normal form? i.e. multivalues

In article <328A9777.795C_at_trader.com>

           danjmcl_at_trader.com "Dan and Clare McLaughlin" writes:
> {snip} During the discussion I stated that a Sybase or Oracle
> database would never be designed using multi-values. I worked briefly
> with a Sybase database in my previous job and as far as I could tell
> this could not be done.

There is a difference between whether something "could" be done, and whether it "should" be done (i.e. is it a good idea to break 3NF?).

I'm sure we have all seen interesting production databases where the design is not what "should" be done according to the textbooks. Some of them work brilliantly. Others are dogs.

> {} My boss claims that you might want to use
> multi-values in a relational database to de-normalize and improve
> performance. I am new to this job and was really shocked when I first
> saw "multi-values" in a so called "relational" Pick/Universe database.
> In school I was trained on the merits of relational databases and how to
> de-nornmalize to improve performance, but I thought all relational
> databases were at least in first normal form. Is a Pick/Universe
> database really relational? Can Sybase and Oracle databases be designed
> using multivalues? I appreciate any comments concerning this.

There is a difference between theory and practice. In theory, you can /should design things in third normal form. In practice, you may throw away your 3NF in some places.

There was an article about performance tuning (Managing Multiple Joins) in Oracle Magazine this month by Eyal Aronoff.

He quoted an example of a master-detail join between 2 tables. In his example, he suggests that in certain circumstances you may want to keep the current detail record in a row in the master table. Example: an employee table joined to salary history, where only the latest salary history is important.

His justification for this is performance.

We distribute DBMS Performance Tools for Oracle (I/Watch, SQLab etc), so we see badly performing SQL all the time. Sometimes its because the developers did not fully comprehend what a relational database should be doing. Other times, we have to persuade them to throw away their text books, de-normalise, and get rid of those inefficient 8-table joins.

-- 
Dennis
----------------------------------------------------------------
Dennis Adams			dennis_at_comsense.com
Common Sense Computing (UK) Ltd	http://www.comsense.com/comsense
Canada House, 272 Field End Rd, Eastcote, Middx HA4 9NA
Phone: +44 181 866 4400		Fax: +44 181 429 4778
#### I/Watch Oracle Performance Monitoring - available now ####