MySQL Database Design
This chapter is from the book
This chapter is from the book
This chapter is from the book
Whenever you are working with a relational database management system such as MySQL, the first step in creating and using a database is to establish its structure. Database design, aka data modeling, is crucial for successful long-term management of your information. Using a process called normalization, you carefully eliminate redundancies and other problems that will undermine the integrity of your data.
The techniques you will learn in this chapter will help to ensure the viability, performance, and reliability of your databases. The example I will use—a record of business transactions such as invoices and expenses—will be referred to in later chapters, but the principles of normalization apply to any database application you might create.
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, and 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 (for the record, 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 used 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 modified to log work hours on projects and so forth. I have created a preliminary listing of the data to record in Table 3.1.
Table 3.1 Based on my intended usage of this database, all of the requisite information to be recorded is listed here.
Accounting Database |
|
|
Item |
Example |
|
Invoice Number |
1 |
|
Invoice Date |
4/20/2002 |
|
Invoice Amount |
$30.28 |
|
Invoice Description |
HTML design Date |
|
Invoice Paid |
5/11/2002 |
|
Client Information |
Acme Industries, 100 Main Street, Anytown, NY, 11111, (800) 555-1234 |
|
Expense Amount |
$100.00 |
|
Expense Category & Description |
Web Hosting Fees-Annual contract for hosting www.DMCinsights.com |
|
Expense Date |
1/26/2002 |