Solved

Why to normalize?

Posted on 2015-02-17
9
84 Views
Last Modified: 2015-02-21
The following image includes some pie-charts to compare and contrast with poorly normalized vs properly normalized table structure.

Please add points not covered or improve where you feel it could be said better. I hope at the end I could improve the charts and the explanations. Also, possibly  add some objective points not covered yet.charts
Here is the text:
In developing a database application, it takes less than 25% of the total time build table structure whereas the other parts such as reports and forms take about 75% of the time. 

But as far as importance is concerned, a sound table structure is over 75% important because a database is like a building structure where sound foundation is a must have. Table structure is the foundation of a database. As a matter of fact, table structure is the database.

As with a good foundation for a building one can build a good building to last many decades. Likewise, with a correct table structure, the application saves time and effort each hour is in use. 

Furthermore, the time and effort in designing the forms and reports is very low and accomplished very quickly. Also, it opens the door to many automation opportunities with huge cost saving possibilities in design time. 

Lastly, if new business requirement becomes available, a properly designed table structure could easily handshake with the new tables to implement the new business functionalities. 
In summation, mastery of normalization is a must for professionals dealing working with data manipulation.

Open in new window

0
Comment
Question by:Mike Eghtebas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 34

Assisted Solution

by:ste5an
ste5an earned 75 total points
ID: 40614794
To avoid anomalies.. thus a a non-normalized database requires always more knowledge about the structure.
0
 
LVL 58
ID: 40614801
From your pie charts, your viewpoint and focus is one from a business perspective?

Jim.
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40614807
I have tried to include business perspective, design, etc. (all). These are subjective points of views.  Please add your subjective or objective views to it. They are whatever that make operation robust and cost effective.

Mike
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 14

Assisted Solution

by:ThomasMcA2
ThomasMcA2 earned 75 total points
ID: 40614853
The benefits of normalization depend on how badly your existing data is not normalized.

Non-normalized data has redundancies, such as the customer name or address in every detail record in your invoice detail file. Instead, there should only be a customer number in the invoice files, which links to the customer master. What happens when a customer moves to a new address? In a non-normalized database, you would have to change the address in every invoice record. In a normalized database, you only change it once, in the customer master file.

Another example is reporting. In a non-normalized database, item # ABC123 may be in thousands of invoice detail records, and each one has their own, manually-typed product description. Which description do you put on the report? In a normalized database, there is only one product description for item # ABC123, and that description is in the item/product master file.
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 125 total points
ID: 40614901
Mike,

 I asked the question because from the titles on your pie charts, you're not looking for the technical reasons why (like insert or update anomalies), but rather what's the result to the business for not doing so (Cost of development, operations, accuracy in reporting, etc).

 Not sure what "Importance" represents....might be best if you went down your list and explained each.   Some are obvious, like "database operations savings", but that covers a wide range; operations within the database, disk space, backup time, needing a larger server because it's inefficient, etc. and you may be thinking of one specific thing when others should be named (like infrastructure costs being split off from operations on a database).

Jim.
0
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 75 total points
ID: 40615157
Why to normalize?
You don't need to,
        if you do the job yourself, and don't mind rewriting.
        if you'r using manual paper work
        if the job is small.
        and you should know that reporting is produced from unnormalized data.

But if dealing with machines, then you simplify storage of info by normalizing. It lets us concentrate on one category of information (say students' info) then shift to another category (students qualifications).
The machine will help us unnormalize the information when required to produce our reports.

INNER JOIN is actually an "unnormalize" process to produce a new table to report from.
0
 
LVL 42

Assisted Solution

by:pcelba
pcelba earned 75 total points
ID: 40615561
I can add some votes for de-normalized data (all of them are coming from the old times):
1) Unstable hardware environment - to have data stored at several places reduces the risk of data loss (I remember the times when we have been reconstructing the database from several damaged disk files)
2) Improved query speed - Imagine a property defined at the top level of some tree which you would need in a query result based on the lowest level of your hierarchy. Normalized data would require many joins to achieve this requirement but de-normalized data will require just a simple select at the lowest level... and the number of joins was restricted in the past... One example (not good probably) - old telephone exchanges where we've stored each screw and wire at the lowest level of the database. Upper levels stored connectors or blocks of screws, then we have had racks and their sections etc. etc. And at some of the upper level is defined the screw-thread (left or right derived from their manufacturer) and you need to create a worklist which reconnects two phones with a note about the screw-thread to avoid possible damage by junior technician... The query was much faster when the info about the screw-thread was at the lowest level.

But we have better SQL engines, more reliable hardware, and EE web so we can do better systems today...

OTOH, I would also say clients do not care about data normalization if the system works.

BTW, what is better:
1) To have Customer number in each Order and separate Customer changes history or
2) To have Customer number and a copy of the address fields in each Order?
0
 
LVL 34

Expert Comment

by:ste5an
ID: 40616196
I've mentioned the anomalies, cause they may lead to wrong results. And nobody likes wrong results.
0
 
LVL 9

Assisted Solution

by:davidanders
davidanders earned 75 total points
ID: 40617098
There is a well regarded white paper about database design from a Filemaker perspective.
White Paper for FMP Novices
http://www.foundationdbs.com/downloads.html
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question