Physical database design translates the logical data model into a set of SQL statements that define the database. For relational database systems, it is relatively easy to translate from a logical data model into a physical database.
Rules for translation:
-
Entities become tables in the physical database.
-
Attributes become columns in the physical database. Choose an appropriate data type for each of the columns.
-
Unique identifiers become columns that are not allowed to have NULL values. These are referred to as primary keys in the physical database. Consider creating a unique index on the identifiers to enforce uniqueness.
-
Relationships are modeled as foreign keys.
Spaces are not allowed in entity names in a physical schema because these names must translate into SQL calls to create the tables. Table names should therefore conform to SQL naming rules.
Because primary key attributes are complete inventions, they can be of any indexable data type. (Each database engine has different rules about which data types can be indexable.) Making primary keys of type INT is almost purely arbitrary.
It is almost arbitrary because it is actually faster to search on numeric fields in many database engines. However, one could just have well have chosen CHAR as the type for the primary key fields. The bottom line is that this choice should be driven by the criteria for choosing identifiers.
| Physical Table Definitions | |||
| Table | Column | Data Type | Notes |
| CD | CDId CDTitle |
INT TEXT(50) |
Primary Key |
| Artist | ArtistId ArtistName |
INT TEXT(50) |
Primary Key |
| Song | SongId SongName |
INT TEXT(50) |
Primary Key |
| RecordLabel | RecordLabelId RecordLabelName |
INT TEXT(50) |
Primary Key |
Model relationships by adding a foreign key to one of the tables involved in the relationship. A foreign key is the unique identifier or primary key of the table on the other side of the relationship.
The most common relationship is the 1-to-M relationship. This relationship is mapped by placing the primary key on the "one" side of the relationship into the table on the "many" side.
1-to-1 relationships should be mapped by picking one of the tables and giving it a foreign key column that matches the primary key from the other table. In theory, it does not matter which table is chosen, but practical considerations may dictate which column makes the most sense as a foreign key.
| Physical Data Model | |||
| Table | Column | Data Type | Notes |
| CD | CDId CDTitle RecordLabelId |
INT TEXT(50) INT |
Primary Key
Foreign Key |
| Artist | ArtistId ArtistName |
INT TEXT(50) |
Primary Key |
| Song | SongId SongName CDId ArtistID |
INT TEXT(50) INT INT |
Primary Key
Foreign Key |
| RecordLabel | RecordLabelId RecordLabelName |
INT TEXT(50) |
Primary Key |
The last remaining task is to translate the complete physical database schema into SQL. For each table in the schema, write one CREATE table statement. Typically, designers create unique indices on the primary keys to enforce uniqueness.
| CREATE table CD (CDId INT NOT NULL, RecordLabelId INT, CDTitle TEXT, PRIMARY KEY (CDId)) |
| CREATE table Artist (ArtistId INT NOT NULL, ArtistName TEXT, PRIMARY KEY (ArtistId)) |
| CREATE table Song (SongId INT NOT NULL, CDId INT, SongName TEXT, PRIMARY KEY (SongId)) |
| CREATE table RecordLabel (RecordLabelId INT NOT NULL, RecordLabelName TEXT, PRIMARY KEY (RecordLabelId)) |
Example script to create the database in MySQL
Data models are meant to be database independent. These techniques and data models may therefore be applied not only to MySQL, but also to Oracle, Sybase, Ingres or any other relational database engine.