Relay-Version: version B 2.10 5/3/83; site utzoo.UUCP
Path: utzoo!utgpu!water!watnot!watmath!clyde!cbatt!cwruecmp!hal!ncoast!allbery
From: allbery@ncoast.UUCP
Newsgroups: comp.databases
Subject: Normalized Databases
Message-ID: <2279@ncoast.UUCP>
Date: Sat, 4-Apr-87 02:01:57 EST
Article-I.D.: ncoast.2279
Posted: Sat Apr  4 02:01:57 1987
Date-Received: Sun, 5-Apr-87 08:46:19 EST
References: <43239@beno.seismo.CSS.GOV>
Reply-To: allbery@ncoast.UUCP (Brandon Allbery)
Followup-To: comp.databases
Distribution: na
Organization: Cleveland Public Access UN*X, Cleveland, Oh
Lines: 67

As quoted from <43239@beno.seismo.CSS.GOV> by tiberio@seismo.CSS.GOV (Mike Tiberio):
+---------------
| At our facility we have a certain large investment in a relational database
| design that is not in the highest normal form. We have some folks proposing
>...
| 3) Explaining the database is simplest.
>...
| While I agree with the first point, If disc space is not a problem I
| don't see where this is a big win.
>...
| I disagree with the third point, especially if I have to explain this
| twisted mess of relations (56 vs 24) to our mythical "joe" user.
+---------------

It *is* easier.  Our database at TDI has very little normalization, being
basically unchanged from its original form as a FilePro-16 (early version)
database, which was not very relational at all.  In particular, the labor
tracking system for factory orders is something like:

	order --->> lineitem
	  \		|
	   \		v
	    ------>  labdesc
		      ^  |
	         ----^   v
	        /	 v
	    routing ->> labordtl
	        \	    ^
	         \	    ^
	          ------>> location

One arrow is a one-to-one, two make a one-to-many.  I also omit the item file
which joins one-to-many with the lineitem and routing tables, and a number of
other relationships.  Normalizing this adds more tables, but the resulting
relationships are MUCH easier to follow.  (I have attempted to chart the
current relationships in our application; talk about spaghetti...)

One thing that must be remembered, however:  Normalization is not ALWAYS
a good idea.  For instance, it can be argued that cities should be ``normal-
ized'' onto states, ditto for zip codes, UPS zones should be normalized to
ZIP codes, and area codes normalized to states; while tables expressing these
relationships produce an increased normalization, they are in practice a bit
ridiculous.  (Hmmm, that last sentence doesn't make much sense even to me.
Maybe I should try again?)  A practical example:  an order entry application
can have a customer mailing address, a customer shipping address, and ways
to override these addresses for specific orders (shipping) and invoices
(mailing).  Normalization says that addresses should be stored in a separate
address file in all cases and pointers used from the various tables; this
saves space in the tables.  However, in practice at least the normal
customer mailing address will be kept in the customer file rather than the
address file, as it will be the most commonly used and needs to be available
for ready reference.  (The UNIFY 3.2 Programmer's Manual contains a very
complete discussion on normalization, when to apply it, and when *not* to
use it; it does a far better job than I am....)

++Brando
-- 
 ____   ______________
/    \ / __   __   __ \   Brandon S. Allbery		|      /^\      USS
 ___  | /__> /  \ /  \    aXcess Company		|     A A A  CHALLENGER
/   \ | |    `--, `--,    6615 Center St. #A1-105	|     H V H
|     | \__/ \__/ \__/    Mentor, OH 44060-4101		|     H . H    SEVEN
\____/ \______________/   +1 216 974 9210		|    /  |  \    WHO
________________________________________________________|   /___|___\  DARED
As long as there is the spirit to dare new frontiers, the dream will never die.
cbatt!cwruecmp!ncoast!allbery ncoast!allbery%case.CSNET@relay.CS.NET BALLBERY
	    (UUCP)                      (CSNET/Internet)             (MCIMail)