Solved

Why to normalize?

Posted on 2015-02-17
9
75 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
9 Comments
 
LVL 32

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 57
ID: 40614801
From your pie charts, your viewpoint and focus is one from a business perspective?

Jim.
0
 
LVL 33

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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 57

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 41

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 32

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 8

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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.

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now