This chapter describes and explains the Physical Model.
Purpose
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.
Who
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
Data Architect
Database Administrator (in smaller shops)
When
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.
Diagram
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
Table
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.
Table
Reference
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.
Reference
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