Rules for Good Database Design

• Tables should not contain redundant (repetitive) data. (If you are repeatedly entering the same numbers or strings in a table, something is wrong.)
• Tables should not have columns like order1, order2, order3. Even if you allow for 10 such columns, the day will come when a customer orders 11 articles.
• The storage requirements for all your tables should be as small as possible.
• Frequently required database queries should be able to be executed simply and efficiently. (One usually notices a violation of this rule only when the database contains not a couple of test records, but thousands or even millions. At that point, a change in the design may no longer be possible.)

These rules have the same import as the normalization rules presented in the next section, but these here are often easier to follow.

Tips for Naming

• MySQL is case-sensitive in regard to database and table names, but not so with column names. It is thus important, at least with your databases and tables, to arrive at a uniform system for using upper- and lowercase letters. (In the example databases in this book, only lowercase letters are generally used for naming databases and tables.)
• Names of databases, tables, and columns can be at most 64 characters long.
• Avoid special characters (e.g., üàû) in names. MySQL allows all alphanumeric characters, but different operating systems and Linux distributions use different default character sets, and changing a system could lead to problems.
• Choose clear field and table names. Take care in naming fields that they describe the content accurately. Thus authName is better than name.
• A uniform naming system for fields can save many careless errors. Whether you prefer author_name or authName is irrelevant, as long as you are consistent.
• Similarly, you should consider how you want to deal with singular and plural. In my tables, I have tried to use plural exclusively. There is no rule as to what is correct, but it is confusing if half your tables use singular, the other half plural.

Tips on the Design Process

It is no easy matter to distribute a collection of data efficiently and intelligently among several tables. Novices in the area of database design should take the following suggestions to heart:

• Begin with a relatively small number of test data, and attempt to enter them in one or more tables. (The scope of the test data should not, however, be so small that obvious design problems go undetected. But it should not be so large that the time taken up in design is too great.)
• Perform your first experiments not with real MySQL tables, but instead, with some worksheets in a table calculation program such as Excel or OpenOffice Calc. (Figure 8-1 gives a preview of the next section.) This allows you to work in a far less complex environment. At this point, you should be focusing on the distribution of data among the tables and their columns, not on database-specific details such as column format and indexes.

Normalization Rules

Why is it that authors of books think about nothing (well, almost nothing) but books? The author begs the indulgence of his readers in that the example in this section deals with books. The goal of the section is to create a small database in which data about books can be stored: book title, publisher, author(s), publication date, and so on. These data can of course, be stored without a database, in a simple list in text format, for example, as appears in the Bibliography at the end of this book:

• Michael Kofler: Linux, 7th edition. Addison-Wesley 2004.
• Michael Kofler, David Kramer: Definitive Guide to Excel VBA, Second Edition. Apress 2003.
• Robert Orfali, Dan Harkey, Jeri Edwards: Client/Server Survival Guide. Addison-Wesley 1997.
• Tobias Ratschiller, Till Gerken: Web Application Development with PHP 4.0. New Riders 2000.

This is a nice and convenient list, containing all necessary information. Why bother with all the effort to transform this text (which perhaps exists as a document composed with some wordprocessing program) into a database?

Needless to say, the reasons are legion. Our list can be easily searched, but it is impossible to organize it in a different way, for example, to create a list of all books by author x or to create a new list ordered not by author, but by title.

The resulting database mylibrary will be improved step by step in the course of this chapter. The finished database is available for download at the Apress website. In addition, the mylibrary database is the basis for countless examples in this book.