A thorough database design process will undergo four distinct phases, as follows:
•
Conceptual: This is the “sketch” of the database that you will get from initial requirements
gathering and customer information. During this phase, you attempt to identify what the
user wants. You try to find out as much as possible about the business process for which you
are building this data model, its scope, and, most important, the business rules that will gov-
ern the use of the data. You then capture this information in a conceptual data model
consisting of a set of “high-level” entities and the interactions between them.
•
Logical: The logical phase is a refinement of the work done in the conceptual phase, trans-
forming what is often a loosely structured conceptual design into a full-fledged relational
database design that will be the foundation for the implementation design. During this
stage, you fully define the required set of entities, the relationships between them, the attrib-
utes of each entity, and the domains of these attributes (i.e., the sort of data the attribute
holds and the range of valid values).
•
Implementation: In this phase, you adapt the logical model for implementation in the host
relational database management system (RDBMS; in our case, SQL Server).
•
Physical: In this phase, you create the model where the implementation data structures are
mapped to physical storage
. This phase is also more or less the performance tuning/optimiza-
tion phase of the project because it is important that your implementation should function in
the same way no matter what the physical har
dware looks like. It might not function very fast,
but it will function. It is during this phase of the project that indexes, disk layouts, and so on,
come into play, and not before this.
The first four chapters of this book are concerned with the conceptual and logical design
phases, and I make only a few references to SQL Server. Generally speaking, the logical model of any
relational database will be the same, be it for SQL Server, Oracle, Informix, DB2, MySQL, or any-
thing else based, in some measure, on the relational model.

such as the following:
People place orders in order to buy products.
Immediately, you can identify three conceptual entities (in bold) and begin to understand how
they interact. Note too, phrases such as “in order” can be confusing, and if the writer of this spec
were writing well, the phrase would have been “
People place orders to buy products.”
■
Note
An entity is not the same thing as a table. A table is an implementation-specific SQL construct. Sometimes
an entity will map directly to a table in the implementation, but often it won’t. Some conceptual entities will be too
abstract to ever be implemented, and sometimes they will map to two or more tables. It is a major (if somewhat
unavoidable because of human nature) mistake at this point of the process to begin thinking about how the final
database will look.
The primary point of this note is simply that you should not rush the design process by worrying about implemen-
tation details until you start to flip bits on the SQL Server. The next section of this chapter will establish the
terminology in more detail. In the end, one section had to come first, and this one won.
During this conceptual phase, you need to do the requisite planning and analysis so that the
requirements of the business and its customers are met. The conceptual design should focus stead-
fastly on the broader view of the system, and it may not correspond to the final, implemented
system. However, it is a vital step in the process and provides a great communication tool for partic-
ipants in the design process.
The second essential element of the conceptual phase is the discovery of
business rules. These
are the rules that govern the operation of your system, certainly as they pertain to the process of
creating a database and the data to be stored in the database. Often, no specific tool is used to doc-
ument these rules, other than Microsoft Excel or Word. It is usually sufficient that business rules are
pr
esented as a kind of checklist of things that a system must or must not do, for example:
• Users in group X must be able to change their own information.
• Each company must have a ship-to address and optionally a bill-to address if its billing

• Applicants should be between 18 and 32 years of age, but you are allowed to accept people of
any age if you have proper permission.
The first rule can easily be implemented in the database. If an applicant enters an age of 17
years or younger, the RDBMS can reject the application and send back a message to that effect.
The second rule is not quite so straightforward to implement. In this case, you would probably
require some sort of workflow process to route the request to a manager for approval. T-SQL code is
not inter
active, and this rule would most certainly be enforced outside the database, probably in
the user interface (UI).
It pays to be careful with any rule, even the first. No matter what the initial rules state, the lee-
way to break the rules is still a possibility. Unfortunately, this is just part of the process. The
important thing to recognize is that every rule that is implemented in an absolute manner can be
trusted, while breakable rules must be verified with every usage.
■
Note
Ideally, the requirements at this point would be perfect and would contain all business rules, processes,
and so forth, needed to implement a system. The conceptual model would contain in some form every element
needed in the final database system. However, we do not live in a perfect world. Users generally don’t know what
they want until they see it. Business analysts miss things, sometimes honestly, but often because they jump to
conclusions or don’t fully understand the system. Hence, some of the activities described as part of building a
conceptual model can spill over to the logical modeling phase.
Logical
The logical phase is a refinement of the work done in the conceptual phase. The output from this
phase will be an essentially complete blueprint for the design of the relational database. Note that
dur
ing this stage y
ou should still think in terms of entities and their attributes, rather than tables
and columns. No consideration should be given at this stage to the exact details of “how” the system
will be implemented. As previously stated, a good logical design could be built on any RDBMS. Core
activities during this stage include the following:

Note
Before we begin to build the logical model, we need to introduce a complete data modeling language. In
our case, we will be using the IDEF1X modeling methodology, described in Chapter 2.
Implementation
During the implementation phase, you fit the logical design to the tool that is being used (in our
case, an RDBMS, namely, SQL Server). This involves choosing datatypes, building tables, applying
constr
aints
, writing triggers, and so on, to implement the logical model in the most efficient man-
ner. This is where platform-specific knowledge of SQL Server, T-SQL, and other technologies
becomes essential.
Occasionally this phase will entail some reorganization of the designed objects to make them
easier to implement or to circumvent some inherent limitation of the RDBMS. In general, I can state
that for most designs there is seldom any reason to stray a great distance from the logical model,
though the need to balance user load and hardware considerations can make for some changes to
initial design decisions. Ultimately, one of the primary goals is that no data that has been specified
or integrity constraints that have been identified in the conceptual and logical phases will be lost.
Data can (and will) be added, often to handle the process of writing programs to use the data. The
key is to not affect the designed meaning or, at least, not to take anything away from that original
set of requirements.
I
t is at this point in the pr
oject that constructs will be applied to handle the business rules that
w
er
e identified dur
ing the conceptual par
t of the design.
These constr
ucts will vary from the

Note
We will discuss Codd’s rules in Appendix A.
It may be that it is necessary to distribute data across different files, or even different servers,
but as long as the published logical names do not change, users will still access the data as columns
in rows in tables in a database.
■
Note
Our discussion of the physical model will be reasonably limited. We will start by looking at entities and
attributes during conceptual and logical modeling. In implementation modeling, we will switch gears to deal with
tables, rows, and columns. The physical modeling of records and fields will be dealt with only briefly (in Chapter 8).
If you want a deeper understanding of the physical implementation, check out
Inside Microsoft SQL Server 2005:
The Storage Engine
by Kalen Delaney (Microsoft Press, 2006) or any future books she may have released by the
time you are reading this.
Relational Data Structures
This section introduces the following core relational database structures and concepts:
• Database and schema
• Tables, rows, and columns
• The Information Principle
• Keys
• Missing values (nulls)
As a person reading this book, this is probably not your first time working with a database, and
as such, you are no doubt somewhat familiar with some of these concepts. However, you may find
ther
e are quite a few points presented here that you haven’t thought about—for example, the fact
that a table consists of unique r
o
ws or that within a single r
o

tain subset of the implemented database.
Once the database is actually implemented, it becomes the primary container used to hold,
back up, and subsequently restore data when necessary. It does not limit you to accessing data
within only that one database; however, managing data in separate databases becomes a mor
e
manual process, rather than a natural, built-in RDBMS function.
■
Caution
The term schema has another common meaning that you should realize: the entire structure for the
databases is referred to as the schema.
Tables, Rows, and Columns
The object that will be involved in all your designs and code is the table. In your designs, a table will
be used to represent
something, either real or imaginary. A table can be used to represent people,
places, things, or ideas (i.e., nouns, generally speaking), about which information needs to be
stor
ed.
The word
table has the connotation of being an implementation-oriented term, for which
Dictionary.com (
) has the following definition:
A
n or
derly arrangement of data, especially one in which the data are arranged in columns
and rows in an essentially rectangular form.
A basic example of this form of table that most people are familiar with is a Microsoft Excel
spreadsheet, such as that shown in Figure 1-1.
CHAPTER 1
■
INTRODUCTION TO DATABASE CONCEPTS8