154
Part II: Building Databases and Working with Data
Some rare situations call for a complex optionality based on a condition. Depending on a
rule, the relationship must be enforced, for example:■ (^) If an organization sometimes sells ad hoc items that are not in the item entity,
the relationship may, depending on the item, be considered optional. The
orderdetail entity can use two attributes for the item. If the ItemID attribute
is used, it must point to a valid item entity primary key.
■ (^) However, if the NonStandardItemDescription attribute is used instead, the
ItemID attribute is left null.
■ (^) A check constraint ensures that for each row, either the ItemID or
NonStandardItemDescription is null.
How the optionality is implemented is up to the SQL DDL Layer. The only purpose of the
conceptual design layer is to model the organization’s objects, their relationships, and their
business rules.
The AdventureWorks2012 database can be downloaded from http://msftdbprodsamples
.codeplex.com/releases/view/55330.
Data-Model Diagramming
Data modelers use several methods to graphically work out their data models. The Chen ER diagramming
method is popular, and Visio Professional includes it and fi ve others. Information Engineering — E/R
Diagramming, is rather simple, easy to understand and explain, and works well on a whiteboard, as
shown in Figure 7-3. The cardinality of the relationship is indicated by a single line or by three lines
(crow’s feet). If the relationship is optional, a circle is placed near the foreign key.
Primary Table Secondary Table
FIGURE 7-3
A simple method for diagramming logical schemas.
Another benefi t of this simple diagramming method is that it doesn’t require an advanced version of
Visio. Visio is OK as a starting point, but it doesn’t give you a nice life cycle like a dedicated modeling
tool. There are several more powerful tools, but it’s actually a personal preference.
Data Design Patterns ..........................................................................................
Design is all about building something new by combining existing concepts or items using
patterns. The same is true for database design. The building blocks are tables, rows, and
columns, and the patterns are one-to-many, many-to-many, and others. This section
explains these patterns.c07.indd 154c07.indd 154 7/30/2012 4:18:12 PM7/30/2012 4:18:12 PM
http://www.it-ebooks.info