Publishers of technology books, eBooks, and videos for creative people

Home > Articles > Web Design & Development

MySQL Database Design

  • Print
  • + Share This
  • 💬 Discuss
This chapter is from the book

This chapter is from the book

The first step in creating and using a database is to establish its structure. The techniques you will learn in this sample chapter will help to ensure the viability, performance, and reliability of your databases.

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


  • + Share This
  • 🔖 Save To Your Account

Discussions

comments powered by Disqus