Re: Normalization was Left AS/400 and Returned

[Alan Campin <Alan.Campin@xxxxxxx>]

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.