O'Reilly logo

Managing & Using MySQL, 2nd Edition by Hugh E. Williams, Randy Yarger, George Reese, Tim King

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Physical Database Design

What was the point in creating the logical data model? You want to create a database to store data about CDs. The data model is only an intermediate step along the way. Ultimately, you would like to end up with a MySQL database in which you can store data. How do you get there? Physical database design translates your logical data model into a set of SQL statements that define your MySQL database.

Since MySQL is a relational database system, it is relatively easy to translate from a logical data model, such as the one we described earlier, into a physical MySQL database. Here are the rules for translation:

  • Entities become tables in the physical database.

  • Attributes become columns in the physical database. Choose an appropriate data type for each column.

  • Unique identifiers become columns that are not allowed to have NULL values. These are called primary keys in the physical database. You may also choose to create a unique index on the identifiers to enforce uniqueness.

  • Relationships are modeled as foreign keys.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required