Chapter 3. Normalizing Your Data

In This Chapter

  • Understanding why single-table databases are inadequate

  • Recognizing common data anomalies

  • Creating entity-relationship diagrams

  • Using DBDesigner to create data diagrams

  • Understanding the first three normal forms

  • Defining data relationships

Databases can be deceptive. Even though databases are pretty easy to create, beginners usually run into problems as soon as they start working with actual data.

Computer scientists (particularly a gentleman named E.F. Codd in the 1970s) have studied potential data problems and defined techniques for organizing data. This scheme is called data normalization. In this chapter, you discover why single-table databases rarely work for real-world data and how to create a well-defined data structure according to basic normalization rules.

Tip

On the CD-ROM, I include a script called buildHero.sql that builds all the tables in this chapter. Feel free to load that script into your MySQL environment to see all these tables for yourself.

Recognizing Problems with Single-Table Data

Packing everything you've got into a single table is tempting. Although you can do it pretty easily (especially with SQL) and it seems like a good solution, things can go wrong pretty quickly.

Table 3-1 shows a seemingly simple database describing some superheroes.

Table 3.1. A Sample Database

Name

Powers

Villain

Plot

Mission

Age

The Plumber

Sewer snake of doom, unclogging, ability to withstand smells

Septic Slime Master

Overcome Chicago with slime

Stop the ...

Get HTML, XHTML, and CSS All-In-One Desk Reference For Dummies® now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.