Re: Normalization was Left AS/400 and Returned

["Booth Martin" <booth@xxxxxxxxxxxx>]

Is there any evidence to support the idea of performance issues with
normalized data on the iSeries?  If there is, is it the problem of the data
design or the application's design?  I regularly see performance problems,
but in every instance the problems were design, not data. 
 
---------------------------------
Booth Martin
http://www.martinvt.com
---------------------------------
-------Original Message-------
 
From: Midrange Systems Technical Discussion
Date: 05/03/05 18:15:37
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Normalization was Left AS/400 and Returned
 
I disagree strongly about not normalizing databases because of performance
issues or using index files.
 
I hear this excuse all the time. "It won't be fast enough." But the rules of
databases development all say the same thing. Build the database normalized
and then denormalize only if you have performance issues. In other words,
don't put the cart before the horse. In my years of creating databases, I
have never seen a situation where a normalized database had to be
denormalized because of performance issue. In fact, the opposite is true.
Normalized database perform better and that is using "Indexed" access or SQL

 
Lets take an example. The guilty party will remain nameless.
 
Software Company wanted to create an order master.
 
Order Header - Ok
 
Order Line - Lets not normalize. Lets put the comments at the same level as
the order line and create a multi-format logical to join together two
different types of comments and the order line and if the line number is
less than the first order line it is a header comment and if the line number
of the comment is between order lines lets make it line comments and then
let us do all of our processing through multi-format logical. So every time
you process, you process through 2 million comment records to get a few
order lines instead of simply
 
Order Header
  K OrderNumber
   Order Comments
     K OrderNumber
     K OrderCommentNumber
  Order Lines
   K OrderNumber
   K OrderLineNumber
     Order Line Comments
   K OrderNumber
       K OrderLineNumber
       K OrderLineCommentNumber
 
This is simple. May require order comments header, also.
 
But wait, this gets better. We can only support one shipment per order. Lets
take the order line and split it apart into multiple lines each time we do a
shipment and it gets better, we can't do multiple warehouse house location
per shipment so lets split the line apart again for each warehouse location
and this split is permanent at pick time. Now try to take and put this mess
into a pick confirmation screen. Try code so complex, I don't think anybody
knew what the hell it did.
 
All this instead of simply normalizing the structure to:
 
Order Line
  K OrderNumber
  K OrderLineNumber
   Order Shipment Detail
    K OrderNumber
    K OrderLineNumber
    K ShipmentNumber
      Order Shipment Warehouse Location
       K OrderNumber
       K OrderLineNumber
       K ShipmentNumber
       K WarehouseTransactionNumber
 
and
 
ShipmentMaster
  K Shipment Number foreign key to ShipmentNumber in Order Shipment Detail.
 
Now you can look at shipments by order line, by shipment, whatever.
 
So what we are saying it we want to create the mess above because it is an
indexed file? I can't tell you the amount of code that was generated to deal
with this mess. Into the 10 of thousands, if not hundreds at least and
insanely complex code just to because no one could normalize the databases?
A normalized database is always simpler to code to than an indexed or SQL.
Always. If the database is done right, the database will do most of the work
 Just a fact of life.
 
And, by the way, every time that I have seen a multi-format logical, it
means one thing. Bad database design.
 
There are two things that IBM should have left out of the AS/400. Multiple
members and multi-format logicals. I have seen more messes created with
these two things than anything else.
 
O'Well, my two cents again.
 
 
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
 
 
.