Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 88
  • Last Modified:

Why to normalize?

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
Mike Eghtebas
Asked:
Mike Eghtebas
6 Solutions
 
ste5anSenior DeveloperCommented:
To avoid anomalies.. thus a a non-normalized database requires always more knowledge about the structure.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
From your pie charts, your viewpoint and focus is one from a business perspective?

Jim.
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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 Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ThomasMcA2Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
hnasrCommented:
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
 
pcelbaCommented:
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
 
ste5anSenior DeveloperCommented:
I've mentioned the anomalies, cause they may lead to wrong results. And nobody likes wrong results.
0
 
davidandersCommented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now