Re: Normalization was Left AS/400 and Returned -- MIDRANGE-L

Isn't normalization design, not data?

And, getting back to my original performance concern:
which would access faster:
1 - A key over customer number and part number in a de-normalized 
order/line file.
2 - A join logical that joins the order/header file with the order/line 
file so that you can see the customer number from the order header file at 

the same time you see the part number of the order line file.  And, keep 
in mind, that a join logical file does not allow keys from more than one 
file, even though I suspect every new release of OS/400 has formed yet 
another DCR requesting this feature.  This might be possible with an index 

on a view in SQL but I don't think that's allowed either.  I know you can 
get the data this way in a normalized database via SQL but it's going to 
do some work under the covers and performance may suffer.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Booth Martin" <booth@xxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
05/04/2005 12:39 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
cc

Subject
Re: Normalization was Left AS/400 and Returned






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.

 

 

.
-- 
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.