Solved

Why to normalize?

Posted on 2015-02-17
9
77 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 33

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

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

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

831 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