2.2 Modeling

Throughout this book, I will be using industry-standard diagrams to illustrate designs. A critical part of relational data architecture is understanding a special kind of diagram called an entity relationship diagram, or ERD. An ERD graphically captures the entities in your problem domain and illustrates the relationships among them. Figure 2-2 is the ERD of the music library database.

Figure 2-2. The ERD for the music library

There are in fact several forms of ERDs. In the style I use in this book, each entity is indicated by a box with the name of the entity at the top. A line separates the name of the entity from its attributes inside the box. Primary key attributes have "PK" after them, and foreign key attributes have "FK" after them.

The lines between entities indicate a relationship. At each end of the relationship are symbols that indicate what type of relationship it is and whether it is optional or mandatory. Table 2-4 describes these symbols.

Table 2-4. Symbols for an ERD

Symbol	Description
	The many side of a mandatory one-to-many or many-to-many relationship
	The one side of a mandatory one-to-one or one-to-many relationship
	The many side of an optional one-to-many or many-to-many relationship
	The one side of an optional one-to-one or one-to-many relationship

Our ERD therefore says the following things:

• One compact disc contains one or more songs.

• One song appears on exactly one compact disc.

• One compact disc features one or more artists.

• One artist is featured on one or more compact discs.

• An artist can optionally be part of one or more artists (bands).

This ERD is a logical representation of the music library. The entities in a logical model are not tables. First of all, you probably noticed there is no composite entity handling the relationship between an artist and a compact disc—I have drawn the relation directly as a many-to-many relationship. Furthermore, all of the entity names and attributes are in plain English. Finally, no foreign keys are shown.

BEST PRACTICE: Develop an ERD to model your problem before you create the database.

The physical data model transforms the logical data model into the tables that will be created in the working database. A data architect works with the logical data model while DBAs (database administrators) and developers work with the physical data model. You translate the logical data model into a physical one by adding join tables, turning domains into database-specific data types, and using table and column names appropriate to your DBMS. Figure 2-3 shows the physical data model for the music library as it would be created in MySQL.

Figure 2-3. The physical data model for the music library