Database Design Phases

Logical Database Design
From a list of requirements (as they are discovered and documented), the team initially identifies a long
list of data elements that they know must be included in the database. They additionally sort the data
elements into groupings, informally at first and then with more rigor, around the entities of importance
to the organization.
Logical design is performed without concern to the specific database management system that will
eventually host the application’s data. The logical database design for a system will look identical
whether the eventual system will be hosted on DB2 on the mainframe or on SQL Server on a Windows
workstation. That isn’t to say that the eventual systems will be identical—it’s just to say that those
differences don’t appear during logical design.

During the logical design phase the development team determines what data needs to be acquired, stored,
and used by a particular application. Simultaneously, the team identifies what functional processes need
to be performed by the application. As each model is refined, it is compared to the other to ensure that
needed data for each process is included in the data model and that each element of data included in the
data model has some identified processes that will create, use, and (usually) modify or purge the data.

For all of the data elements collected in this phase, it is necessary to begin documenting some basic
characteristics of the data. You need to know where the data comes from (if it isn’t being created by the
new application), and you will want to gather some preliminary estimates of the type of data:
❑ Is it strictly numeric? Will it always be integers or allow decimal values?
❑ Is the data item a date? If so, what granularity is required . . . to the day? to the minute? to the
millisecond?
❑ Will the data be alphanumeric or will it need to include other characters?
❑ What if a needed element isn’t character data but an image or some other unstructured object? I
so, how large do you expect each instance to be?
❑ Is there a defined domain (range or list of allowable values) associated with the data? Does the
domain relate to other data expected to be in your system? Does the domain have an external
definition rule (such as spatial coordinates limited to valid latitude and longitude pairs)?
❑ What is the expected lifetime of this particular data element? Is it transitory or does it continue
to exist for a predictable period of time?
Gathering an initial shopping list of data elements is a necessary but relatively casual process during the
early parts of logical system design. Some discipline, however, is soon needed. The primary means of
organizing this rapidly growing list of data elements is normalization and is traditionally documented
using Entity-Relationship Diagram (ERD) techniques. Both ERDs and the process of normalization are
covered in more detail in a later section of this chapter.