272
Part II: Building Databases and Working with Data
■ (^) Use views to denormalize or fl atten complex joins and hide any surrogate keys used
to link data within the database schema. A well-designed view invites the user to
get right to the data of interest.
■ Save complex aggregate queries as views. Even power users appreciate a well-
crafted aggregate query saved as a view.
Best Practice
Views are an important part of the abstraction puzzle; be intentional in their use. Some developers are
enamored with views and use them as the primary abstraction layer for their databases. They create
layers of nested views or stored procedures that refer to views. This practice serves no valid purpose,
creates confusion, and requires needless overhead.
Data within a normalized database is rarely organized in a readily available format. Building ad hoc que-
ries that extract the correct information from a normalized database is a challenge for most end users.
A well-written view can hide the complexity of the underlying data structures and present the correct
data to the user. The following is a list of best practices that could be used when creating your views.
■ Plan generic, but standard, naming conventions for all views and column aliases.
■ (^) Use aliases to change cryptic column names to recognizable column names. Just
as the SELECT statement can use column or table aliases to modify the names of
columns or tables, these features may be used within a view to present a more read-
able recordset to the user.
■ (^) Include only the columns of interest to the user. When columns that don’t concern
users are left out of the view, the view is easier to query. The columns included in
the view are called projected columns, meaning they project only the selected data
from the entire underlying tables.
■ (^) Plan generic, dynamic views that will have long, useful lives. Single-purpose views
quickly become obsolete and clutter the database. Build the view with the inten-
tion that it will be used with a WHERE clause to select a subset of data. The view
should return all the rows if the user does not supply a WHERE restriction. For
example, the vEventList view returns all the events; the user should include a
WHERE clause to select the local events, or the events in a certain month.
■ (^) If a view is needed to return a restricted set of data, such as the next month’s
events, then the view should calculate the next month so that it can continue to
function over time. Hard-coding values such as a month number or name would be
poor practice.
■ (^) If the view selects data from a range, then consider writing it as a user-defi ned func-
tion (see Chapter 18, “Building User-Defi ned Functions”), which can accept parameters.
■ (^) Consolidate data from across a complex environment. Queries that need to collect
data across multiple servers are simplifi ed by encapsulating the union of data from
c11.indd 272c11.indd 272 7/30/2012 4:41:06 PM7/30/2012 4:41:06 PM
http://www.it-ebooks.info