Many developers create applications without a single thought about
database design. This usually results in inflexible, inefficient
applications. I strongly believe that you must have an intimate
knowledge of the goals of your application, before you build it. Only
when you know what you want to build can you properly design your
database to accommodate your application. Designing a database means
that you must visualize the relationships between the chunks of data
you will be using in your application, and create your tables with
these relationships in mind. The lessons in this hour provide a
foundation for understanding relationships and creating these related
database tables.

I hold a strong belief in MySQL as the best database system for most
Web-based applications, because of it's speed, reliability and cost --
or, its lack of a cost. Better yet, the learning curve is not very
steep! Because MySQL closely follows both the ANSI SQL and ODBC SQL
standards, new users face little confusion when coming to MySQL from
another database system. If you choose to work with another database
system down the road, the knowledge gained from working with MySQL
will serve you well.

The purpose of this book is to bring a new user up to speed with
MySQL. The series of lessons teach the use of MySQL from the ground
up, beginning with basic database design concepts to an introduction
to the SQL syntax, then move into using built-in functions, thinking
about transactional processing and interfacing with MySQL through Perl
and PHP. No previous knowledge of anything database-related is
assumed, and if it's your first foray into databases, you will have a
huge sense of accomplishment when you finish this book.

I had so much fun writing this book because I use MySQL every single
day and like to step into technology evangelist mode whenever
possible. This book provides a solid foundation for planning,
implementing and maintaining your MySQL-driven applications, but the
only real way to enhance your learning is by practice. Each lesson in
this book provides plenty of hands-on practice, and will jump-start
your brain into thinking of things to try on your own.

This chapter is from the book

This chapter is from the book

In this hour, you'll learn the thought processes behind designing a relational
database. This will be the last theory-focused hour; you'll soon be ready
to jump headlong into creating MySQL databases for use in your own applications.

Topics covered in this hour are:

Some advantages to good database design

Three types of table relationships

How to normalize your database

How to implement a good database design process

The Importance of Good Database Design

Good database design is crucial for a high performance application, just like
an aerodynamic body is important to a racecar. If the racecar doesn't have
smooth lines, it will produce drag and go slower. The same holds true for
databases. If a database doesn't have optimized
relationshipsnormalizationit won't be able to perform as
efficiently as possible.

Beyond performance is the issue of maintenance. Your database should be easy
to maintain. This includes storing a limited amount (if any) of repetitive data.
If you have a lot of repetitive data and one instance of that data undergoes a
change (such as a name change), that change has to be made for all occurrences
of the data. To eliminate duplication and enhance your ability to maintain the
data, you would create a table of possible values and use a key to refer to the
value. That way, if the value changes names, the change occurs only oncein
the master table. The reference remains the same throughout other tables.

For example, suppose you are responsible for maintaining a database of
students and the classes in which they're enrolled. If thirty-five of these
students are in the same class, called "Advanced Math," this class
name would appear thirty-five times in the table. Now, if the instructor decides
to change the name of the class to "Mathematics IV," you must change
thirty-five records to reflect the new name of the class. If the database were
designed so that class names appeared in one table and just the class ID number
was stored with the student record, you would only have to change one
recordnot thirty-fivein order to update the name change.

The benefits of a well-planned and designed database are numerous, and it
stands to reason that the more work you do up front, the less you'll have
to do later. A really bad time for a database redesign is after the public
launch of the application using italthough it does happen, and the results
are costly.

So, before you even start coding your application, spend a lot of time
designing your database. Throughout the rest of this hour, you'll learn
more about relationships and normalization, two important pieces to the design
puzzle.