Learning PHP & MySQL, 2nd Edition

Chapter 8. Database Best Practices

Now that you have MySQL up and running and have created a database, let’s talk about database design and backing up your databases. As you know, backing up your data is important. Adding MySQL to PHP and combining the applications for your dynamic web site is a great start. But it helps tremendously to structure your database correctly. If you have security, data integrity, and backups, you have the most crucial pieces of a database. We’ll discuss security in Chapter 15.

Database Design

Designing your database properly is critical to your application performing well. Just as putting the printer all the way across your office is inefficient, placing data in poor relationships makes work less efficient because your database server will waste time looking for data. When thinking about your database, think about what kinds of questions will be asked when your database is used. For example, what are the details about a product for sale? Or, is this a valid username and password?

Relational Databases

MySQL is a relational database. An important feature of relational systems is that data can be spread across several tables, as opposed to our flat-file phone book example. Related data is stored in separate tables and allows you to put them together by using a key common to both tables. The key is the relation between the tables. The selection of a primary key is one of the most critical decisions you’ll make in designing a new database.

The most important concept ...