Normalization

Normalization was developed by an IBM researcher named E.F. Codd in the early 1970s (he also invented the relational database). A relational database is merely a collection of data, organized in a particular manner; Dr. Codd created a series of rules called normal forms that help define that organization. In this chapter I will discuss the first three of the normal forms, which is sufficient for most database designs.

Before you begin normalizing your database, you must define the role of the application being developed. Whether it means that you thoroughly discuss the subject with a client or figure it out for yourself, understanding how the information will be accessed dictates the modeling. Thus, this chapter will require paper and pen, rather than the MySQL software itself (to be clear, database design is applicable to any relational database, not just MySQL).

Database design texts commonly use examples such as music or book collections (indeed, I use the latter in my book PHP Advanced for the World Wide Web: Visual QuickPro Guide), but I will create a more business-oriented accounting database here. The primary purpose of the database will be to track invoices and expenses, but it could easily be expanded to log work hours on projects or whatever. Table 3.1 reflects a sample record showing the type of data to be stored.

Table 3.1. Based on my intended usage of this database, all of the requisite information to be recorded is listed here.

Accounting Data
Item	Example
Invoice Number	1
Invoice Date	4/20/2006
Invoice Amount	$30.28
Invoice Description	HTML design
Date Invoice Paid	5/11/2006
Client Information	Acme Industries, 100 Main Street, Anytown, NY, 11111, [phone]
Expense Amount	$100.00
Expense Category & Description	Web Hosting Fees-Annual contract for hosting www.DMCinsights.com
Expense Date	1/26/2006

Before designing the database, some basic terminology is required. The next two sections will discuss the notions of keys and relationships.

Tips

• One of the best ways to determine what information should be stored in a database is to determine what questions will be asked of the database and what data would be included in the answers.

• Although I will demonstrate manual database design, ready-made applications for this express purpose are listed in Appendix C, "Resources."