Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Why to normalize?

Posted on 2015-02-17
9
Medium Priority
?
85 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 35

Assisted Solution

by:ste5an
ste5an earned 300 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 14

Assisted Solution

by:ThomasMcA2
ThomasMcA2 earned 300 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 500 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 31

Assisted Solution

by:hnasr
hnasr earned 300 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 43

Assisted Solution

by:pcelba
pcelba earned 300 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 35

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 10

Assisted Solution

by:davidanders
davidanders earned 300 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

722 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