Headlines:
Applied Tips:
Additional Links:
applied tips : april 2007
microsoft access : is your data normal?
If you work with databases, it's only a matter of time until you hear the term normalization. Perhaps someone has tried to impress you with his or her vast database knowledge by asking you, "Is that database normalized?"
So what is normalization? Basically, normalization is the process of structuring the tables in a database for maximum efficiency. Normalization involves creating tables and establishing relationships between those tables to make the database more flexible by eliminating redundancy and inconstancies.
Why normalize?
Normalization eliminates redundancy, or the needless repetition of database information. Redundant information wastes space and resources; furthermore, it increases the likelihood of errors. For example, changing an employee name in a normalized database is easy; you simply make the change in one place. Things aren't as easy in a non-normalized database, as the employee name might be stored in several different tables.
Finally, normalized databases are easier to scale, or grow larger in terms of size and functionality. Of course, in the real world it is often unnecessary to adhere to strict normalization rules. For example, typical non-normalized databases often have several phone number fields, such as work and fax, in the same table. Strict database normalization zealots will tell you this breaks database normalization rules; the phone number field should be stored in its own table.
So what are the rules?
Actually they're called forms. There are five normal forms, labeled 1NF through 5NF, that are used in a series of steps to normalize a database. If a database meets the first rule, it is said to be in "first normal form". You only need to worry about the first three forms; leave the fourth and fifth normal forms to the database zealots. Let's examine each of the three normal forms, starting with first normal form.
First Normal Form (1NF)
First normal form sets the most basic rules for the database:
Eliminate any duplicate or repeating columns or groups in the same table.
The following table has some serious problems: For example, what would happen if a student enrolled in a fourth class? You'd you have to add yet another field. Furthermore, this table is prone to data entry errors. For example, changing the name of "Art 203" to "Art 303" would require you to go into several records, making it easy to miss one.
Since each student can have several classes, the classes should be listed in a separate table:
Second Normal Form (2NF)
Once you've established first normal form, you're ready to normalize to second normal form:
Eliminate redundant data.
The following table is an improvement over the first, but it still has some problems: The Room field is being repeated. If this is a large database, updating several thousand records to reflect a room change is difficult.
This problem can be corrected by separating breaking the table into two:
Third Normal Form (3NF)
Third normal form often occurs as a result of normalizing to second normal form. Third normal form states:
Eliminate fields that do not depend on the primary key.
The Name field in the following table should be dependent on the Student
ID primary key.
To correct the problem, we can make the Name field dependent on the Student ID primary key and then remove it from the original table, as follows:
Normalization is so important and useful that you should at least always consider, if not fully implement, the first three forms whenever you create all but the simplest databases.
Remember also that normalization is only a guideline and can sometimes be impractical, if not altogether undesirable to implement.
schedule a class on access
Request an on-site Access class from Applied Office. Sessions are priced per hour, not per person, and your employees can be shown how to maximize their use of queries, forms, reports, and even Visual Basic for Applications (VBA). Learn more here
quick reference card
Get the Quick Reference Card on Microsoft Access! Download it for free and print it on your own printer. You might even want to laminate it.