Microsoft® SQL Server® 2012 Bible

149

Chapter 7: Relational Database Design and Creating the Physical Database Schema

7

The goal is to simply collect all the possible entities and their attributes. At this early

stage, it’s also useful to document as many known relationships as possible, even if those

relationships will be edited several times.

Generalization

Normalization has a reputation of creating complex and unwieldy databases. It’s true that

some database schemas are far too complex, but normalization, by itself, isn’t the root

cause.

The difference between elegant databases that are a joy to query and overly com-

plex designs that make you want to polish your resume is the data modeler’s view of

entities.

When identifying entities, there’s a continuum, as illustrated in Figure 7-1, ranging from a

broad all-inclusive view to a specifi c, narrow defi nition of the entity.

FIGURE 7-1

You can identify entities along a continuum, from overly generalized with a single table, to

overly specifi c with too many tables.

Result:

• Data-driven design


• Fewer tables
  Overly • Easier to extend
  Simple

One Table Specific

Tables

Overly

Complex

The overly simple view groups together entities that are different types of things, for

example, storing machines, products, and processes in the single entity. This approach

might risk data integrity for two reasons. First, it’s diffi cult to enforce referential integrity

(foreign key constraints) because the primary key attempts to represent multiple types

of items. Second, these designs tend to merge entities with different attributes, which

means that many of the attributes (columns) won’t apply to various rows and will simply

be left null. Many nullable columns means the data will probably be sparsely fi lled and

inconsistent.

At the other extreme, the overly specifi c view segments entities that could be represented

by a single entity into multiple entities, for example, splitting different types of subassem-

blies and fi nished products into multiple different entities. This type of design risks fl ex-

ibility and usability:

c07.indd 149c07.indd 149 7/30/2012 4:18:11 PM7/30/2012 4:18:11 PM

http://www.it-ebooks.info