The naming convention of the physical data model has some differences with the naming convention of the logical data model:
- We will use lowercase for tables, columns, and indexes, for example, the user table. And we will use underscore to connect multiple words, for example, the card_list table.
- For foreign key definition, the key's name starts with fk_. The naming convention of the foreign keys is fk_<referencing table name>_<referenced table name>_<referencing field name>. For example, the foreign key of the user_id field in the activity table is fk_activity_user_user_id.
- For index definition, the index name will end with _idx, or _uidx if it is a unique index.
The reason we use lowercase is that MySQL is case-sensitive in Linux/Unix and not case-sensitive in Windows. Making everything in lowercase can avoid issues caused by a mixture of difference cases.
The figure here is that of the physical data model of the TaskAgile application:
The diagram created with MySQL Workbench is an enhanced-entity-relationship (EER) diagram. It uses solid lines to represent identifying relationships and dash lines to represent non-identifying relationships.
An identifying relationship means a child table cannot be uniquely identified without the parent table. For example, in the board_member table, the board_id column and the user_id column is the primary key. And, without a board ID or a user ID, a board member record cannot be uniquely identified. In other words, a relationship is an identifying relationship when the foreign key is part of the child table's primary key.
And, with MySQL Workbench, we can export our physical data model as a DDL script that can be used to set up our database.