Third Normal Form
A database is in Third Normal Form (3NF) if it is in 2NF and every non-key column is independent of every other non-key column. In other words, the fields of a table other than the keys should be mutually independent.
If you followed the first two normalization steps properly, you will not necessarily need to make any changes at this stage. However, if you made a number of changes along the way (as can happen), this could be a last check. For example, say I wanted to record a contact name and email address with each invoice (Figure 3.10). The problem is that this information relates not to an invoice but to the client and, therefore, the database would fail the 3NF test.
That example aside, you may frequently find that the 3NF rules do not change your model at all. That's fine and not uncommon. For that matter, in some cases, 3NF may be overkill and would therefore be ignored (see the sidebar for more on that idea).
To make a database 3NF compliant:
1. | Identify any fields that violate the 3NF rule.
As I stated, my additions to the Invoices table are a problem.
| 2. | Create new tables or move fields accordingly ( Figure 3.11).
The correct structure would be to add the new contact fields to the Clients table. | 3. | Assign or create new primary and foreign keys, if necessary.
In this particular example, no further changes are required.
| 4. | Double-check the design for 1NF, 2NF, and 3NF compliance.
The Accounting design is good to go in my opinion. In Chapter 4, you'll learn about choosing MySQL data types, naming rules, and how to actually create the database in MySQL.
|
 Tips
Once you've sketched out a database on paper, you could create a series of spreadsheets that reflect the design (or use an application specifically tailored to this end). This file can act both as a good reference for the Web developers working on the database as well as a nice thing to give over to the client when the project is completed. Once MySQL begins enforcing the implications of foreign keys for all storage engines (in version 5.1 or later), normalizing your database will be even more necessary than it currently is.
|
As much as ensuring that a database is in 3NF will help guarantee stability and endurance, you won't necessarily normalize every database with which you work. Before undermining the proper methods though, understand that doing so may have devastating long-term consequences.
In the Accounting example, I've left my date columns in a debatably non-atomic format (each stores three pieces of information: a day, a month, and a year). I know this won't be a problem because MySQL has a date column type that's easy to work with. As another example, if you have a table listing employees, with a gender column, you'd probably not make a separate gender table, even though the values Male and Female would be repeated countless times. There are only two gender options, there'll never be a third, and the likelihood of renaming one of these optionschanging Male to Fip or whateveris very low.
The two primary reasons to overrule normalization are convenience and performance. Fewer tables are easier to manipulate and comprehend. Further, because of their more intricate nature, normalized databases will most likely be slower for updating, retrieving data from, and modifying. Normalization requires that you favor data integrity and scalability over simplicity and speed. On the other hand, there are ways to improve your database's performance but few to remedy corrupted data that can result from poor design.
|
|
Js editor
Website development