Skip to main content

Physical Database Design

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
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.