146
Part II: Building Databases and Working with Data
SQL Server developers generally refer to database elements as tables, rows, and columns
when discussing the SQL Data Defi nition Language (DDL) layer or physical schema and
sometimes use the terms entity, tuple, and attribute when discussing the logical design.
The rest of this book uses the SQL terms (table, row, and column), but this chapter is
devoted to the theory behind the design, so the relational algebra terms (entity, tuple, and
attribute) are also used.Database Design Phases
Traditionally, data modeling has been split into two phases: the logical design and the
physical design. However, after spending countless hours designing relation databases and
listening to several lectures on database design the authors are convinced that there are
three phases to database design. To avoid confusion with the traditional terms, they are
defi ned as follows:■ Conceptual model: The fi rst phase digests the organizational requirements and
identifi es the entities, their attributes, and their relationships. During this phase
every opportunity should be taken to collect any information that may have or has
any relevance to the project.
The conceptual diagram model is great for understanding, communicating, and
verifying the organization’s requirements. The diagramming method should be eas-
ily understood by all the stakeholders — the subject-matter experts, development
team, and management. Visio or some similar diagramming tool can assist to pro-
vide a visual aspect to the conceptual model.
At this layer, the design is implementation-independent: It could end up on Oracle,
SQL Server, or even Access. Some designers refer to this as the “logical model.”■ (^) SQL DDL Layer: This phase concentrates on performance without losing the fi del-
ity of the logical model as it applies the design to a specifi c version of a database
engine — SQL Server 2012, for example, generating the DDL for the actual tables,
keys, and attributes. Typically, the SQL DDL Layer generalizes some entities and
replaces some natural keys with surrogate computer-generated keys.
Typically, database developers realize the need for additional tables (entities) and
their corresponding attributes and keys. As a result, the SQL DDL layer might look
different than the conceptual model.
■ (^) Physical layer: The implementation phase considers how the data will be physically
stored on the disk subsystems using indexes, partitioning, and materialized views.
Changes made to this layer won’t affect how the data is accessed, only how it’s
stored on the disk.
The physical layer ranges from simple, for small databases (under 20Gb), to com-
plex, with multiple fi les and fi legroups, indexed views, and data routing partitions.
This chapter focuses on designing the conceptual model, with a brief look at normalization
followed by a repertoire of database patterns.
c07.indd 146c07.indd 146 7/30/2012 4:18:10 PM7/30/2012 4:18:10 PM
http://www.it-ebooks.info