Managing & Using MySQL, 2nd Edition

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.