MySQL: Learning the Database Design Process
- The Importance of Good Database Design
- Types of Table Relationships
- Understanding Normalization
- Following the Design Process
- Summary
- Q&A
See all Sams Teach Yourself on InformIT Database Tutorials.
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.
—Julie Meloni
This chapter is from the book
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.