09-04-2011, 03:04 PM
[attachment=11936]
Database Normalization
About Me
• Member of the MySQL AB documentation team
• MySQL Core and Pro Certified
• Top MySQL expert at experts-exchange.com
• Resident MySQL expert at SearchDatabase.com
• http://openwinmike/aboutme.php
About You
• Currently use MySQL?
• Another RDBMS?
• Are responsible for database design?
• Will be in the future?
• Know about database normalization?
About This Session
• http://openwinmike/presentations/
• http://dev.mysqltech-resources/articles/intro-to-normalization.html
• Introduction
• What Is Database Normalization?
• What are the Benefits of Database Normalization?
• What are the Normal Forms?
• First Normal Form
• Second Normal Form
• Forming Relationships
• Third Normal Form
• Joining Tables
• De-Normalization
• Conclusion
What Is Database Normalization?
• Cures the ‘SpreadSheet Syndrome’
• Store only the minimal amount of information.
• Remove redundancies.
• Restructure data.
What are the Benefits of Database Normalization?
• Decreased storage requirements!
1 VARCHAR(20)
converted to 1 TINYINT UNSIGNED
in a table of 1 million rows
is a savings of ~20 MB
• Faster search performance!
– Smaller file for table scans.
– More directed searching.
• Improved data integrity!
What are the Normal Forms?
• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Boyce-Codd Normal Form (BCNF)
• Fourth Normal Form (4NF)
• Fifth Normal Form (5NF)
Our Table
First Normal Form
• Remove horizontal redundancies
– No two columns hold the same information
– No single column holds more than a single item
• Each row must be unique
– Use a primary key
• Benefits
– Easier to query/sort the data
– More scalable
– Each row can be identified for updating
One Solution
Satisfying 1NF
Forming Relationships
• Three Forms
– One to (zero or) One
– One to (zero or) Many
– Many to Many
• One to One
– Same Table?
• One to Many
– Place PK of the One in the Many
• Many to Many
– Create a joining table
Joining Tables
Our User Table
Second Normal Form
• Table must be in First Normal Form
• Remove vertical redundancy
– The same value should not repeat across rows
• Composite keys
– All columns in a row must refer to BOTH parts of the key
• Benefits
– Increased storage efficiency
– Less data repetition
Satisfying 2NF
Third Normal Form
• Table must be in Second Normal Form
– If your table is 2NF, there is a good chance it is 3NF
• All columns must relate directly to the primary key
• Benefits
– No extraneous data
Satisfying 3NF
Finding Balance
Joining Tables
• Two Basic Joins
– Equi-Join
– Outer Join (LEFT JOIN)
• Equi-Join
– SELECT user.first_name, user.last_name, email.address
FROM user, email
WHERE user.user_id = email.user_id
• LEFT JOIN
– SELECT user.first_name, user.last_name, email.address
FROM user LEFT JOIN email
ON user.user_id = email.user_id
De-Normalizing Tables
• Use with caution
• Normalize first, then de-normalize
• Use only when you cannot optimize
• Try temp tables, UNIONs, VIEWs, subselects first
Conclusion
• http://dev.mysqltech-resources/articles/intro-to-normalization.html
• MySQL Database Design and Optimization
– Jon Stephens & Chad Russell
– Chapter 3
– ISBN 1-59059-332-4
– http://openwinmike/books
• http://openwinmike/presentations