Nip DATA Logo  


ByteDesigner icon Byte Designer

Professional database modeling

Chapter 11

Physical Model

About this chapter

This chapter describes and explains the Physical Model.


The PM is an important vehicle used within the System Development Life Cycle (SDLC) to design, construct, document and communicate the physical structures of a database. This is where database specific features, such as databases specific data types, are introduced.


The PM is created by people who have intimate knowledge of the target database. The person is usually a senior and highly experienced technical person. The PM is derived from the Logical Model (LM).

Examples of people who may be responsible for the creation of a PM;
  • Database Analyst
  • Database Designer
  • Byte Designer
  • Database Administrator (in smaller shops)


The PM is usually created at the start of the Development Phase of the SDLC and is always created after the LM has been completed and approved.


The PM is diagram centric. This means that a PM is most commonly manipulated by laying out tables on a diagram and then showing the references between those tables. A diagram is a very useful tool for understanding and communicating the;
  • tables being addressed
  • references between tables
  • complexity of the Physical Model
  • elegance of the Physical Model
The PM diagram is commonly referred to as an Entity Relationship Diagram (ERD). An ERD may be a Logical Model or a Physical Model.

physical model
Physical Model


The Table is a; person, place, thing or concept which is considered relevant to the system and about which data is stored. The Table is represented in a diagram by a simple box containing the table name and, usually, a list of Columns. Tables are most often derived from Entities but may also exist to support special Relationships or for optimization.



The Reference indicates how two tables are related. The Reference is represented in a diagram by a line connecting the tables in question. The arrow points from the child table to the parent table. The child table will contain one, or more, columns which are used to support the reference - usually called a Foreign Key.


Data Dictionary

A Data Dictionary report can be generated from the PM. This report contains information about all; Tables, References, Columns, Domains, etc in the PM. A Data Dictionary generated from a PM is a usefull method to communicate the PM to others.

Physical Model To Logical Model

Typically; a PM is derived from a LM but there are occasions when a LM is derived from a PM. The most common reason is when a database already exists and no current LM exists for it. In this case a PM is created by Reverse Engineering the existing database. Then the LM is derived from the PM. This is done early in the SDLC - during a Current Situation Analysis.

Another reason is when the PM has deviated from the specification in the LM. This, of course, would require approval by those who signed off on the LM and is needed to keep the LM up-to-date.

Keeping the Logical Model and Physical Models seperate is important because;
  • different people are responsible for them
  • different skill sets go into them
  • the original LM remains, unaltered, as the specification for the PM