Learning MySQL, 2nd Edition

Chapter 2. Modeling and Designing Databases

When implementing a new database, it’s easy to fall into the trap of quickly getting something up and running without dedicating adequate time and effort to the design. This carelessness frequently leads to costly redesigns and reimplementations down the road. Designing a database is like drafting the blueprints for a house; it’s silly to start building without detailed plans. Notably, good design allows you to extend the original building without pulling everything down and starting from scratch. And as you will see, bad designs are directly related to poor database performance.

How Not to Develop a Database

Database design is probably not the most exciting task in the world, but indeed it is becoming one of the most important ones. Before we describe how to go about the design process, let’s look at an example of database design on the run.

Imagine we want to create a database to store student grades for a university computer science department. We could create a Student_Grades table to store grades for each student and each course. The table would have columns for the given names and the surname of each student and each course they have taken, the course name, and the percentage result (shown as Pctg). We’d have a different row for each student for each of their courses:

+------------+---------+-----------------------+------+ | GivenNames | Surname | CourseName | Pctg | +------------+---------+-----------------------+------+ ...