DataArchitect icon Data Architect

Professional database modeling


Chapter 8

Concepts

About this chapter

This chapter is an introduction to some of the concepts used by Data Architect.

Entity Relationship Diagram (ERD)

An ERD is a relatively simple diagram which shows the central elements of a database and the relationships those elements have to each other. The most used database element is the Entity and a Relationship. The Entity is typically represented in the diagram, as some form of box while the Relationships are some form of line connecting two Entities.

ERD's are  extremely useful to quickly understand many aspects of a database design. For example;

Logical Model (LM)

The Logical Model is a type of ERD which depicts the database structures without regard to the specific database for which it will ultimately be implemented in. This allows for database design in the very early stages of system development and provides a way to 'flesh-out' the design without the encumbrances associated with implementation details. The LM, once complete, is morphed into a Physical Model where it can be optimized for a specific database.

Logical Model
figure 1
Logical Model

Physical Model (PM)

The Physical Model is a type of ERD which deals with a specific implementation of a database design. For example; the PM may be for a MySQL or a DB2 database. The PM is database specific because it often uses features, such as particular data types, only found in the specific database. The database elements are usually referred to by slightly different names in a PM as the following chart shows;

Logical Model
Physical Model
Description
Entity
Table
a logical grouping of related data
Relationship
Reference
shows the relationship between two entities or tables
Attribute
Column
also known as a Field

Data Architect is ERD centric. This means that the creation of an ERD is the first thing you will do in Data Architect.

Physical Model
figure 2
Physical Model

Figure 2 shows a simple example of a Physical Model containing two Tables and a Reference. The Tables use table and column names which consider the target database. The column data-types may also use types found only in the target database. The Reference shows that Customers can have Orders and shows that we must use tbOrders.fkCustomer and tbCustomers.pkCustomer to navigate between these two tables. The Parent table is tbCustomers (because that is where the line arrow points) and the Child table is tbOrders.