Microsoft® SQL Server® 2012 Bible

145

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

7

with automated data collection in manufacturing, bar codes in retailing, databases sharing

more data, and consumers placing their own orders on the Internet, the effort required to

enter the data has also decreased.

This chapter presents the relational database design principles and patterns used to develop operational, or online

transaction processing (OLTP), databases.

Some of the relational principles and patterns may apply to other types of databases, but databases not used for

fi rst-generation data (such as most BI, reporting databases, data warehouses, or reference data stores) do not nec-

essarily benefi t from normalization.

In this chapter, the term “database” exclusively refers to a relational, OLTP-style database.

Tables, Rows, Columns

A relational database collects related, or common, data in a single list. For example, all the

product information may be listed in one table and all the customers in another table.

A table appears similar to a spreadsheet and is constructed of columns and rows. The appeal

(and the curse) of the spreadsheet is its informal development style, which makes it easy

to modify and add to as the design matures. Managers tend to store critical information in

spreadsheets, and many databases started as informal spreadsheets.

In both a spreadsheet and a database table, each row is an item in the list and each column

is a specifi c piece of data concerning that item, so each cell should contain a single piece of

data about a single item.

Whereas a spreadsheet tends to be free-fl owing and loose in its design, database tables

should be consistent in terms of the meaning of the data in a column. Because row and col-

umn consistency is important to a database table, the design of the table is critical.

Over the years, different development styles have referred to these concepts with various

different terms, as listed in Table 7-1.

TABLE 7-1 Comparing Database Terms

Development Style List of Common Items Item in the List

Piece of

Information

in the List

Legacy software File Record Field

Spreadsheet Spreadsheet/worksheet/named

range

Row Column/

cell

Relational algebra/

logical design

Entity, or relation Tuple (rhymes

with couple)

Attribute

SQL DDL design Table Row Column

Object-oriented

design

Class Object instance Property

c07.indd 145c07.indd 145 7/30/2012 4:18:09 PM7/30/2012 4:18:09 PM

http://www.it-ebooks.info