A developer's guide to data modeling for SQL server : covering SQL server 2005 and 2008

276 Pages·2008·2.095 MB·English

by  Eric Johnson, Joshua Jones

A Developer’s Guide
to Data Modeling
for SQL Server
C SQL S
OVERING ERVER
2005 2008
AND
Eric Johnson
Joshua Jones
Upper Saddle River, NJ • Boston • Indianapolis • San Francisco
New York • Toronto • Montreal • London • Munich • Paris • Madrid
Capetown • Sydney • Tokyo • Singapore • Mexico City
Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks.
Where those designations appear in this book, and the publisher was aware of a trademark claim, the designations
have been printed with initial capital letters or in all capitals.
The authors and publisher have taken care in the preparation of this book, but make no expressed or implied war-
ranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for incidental or
consequential damages in connection with or arising out of the use of the information or programs contained herein.
The publisher offers excellent discounts on this book when ordered in quantity for bulk purchases or special sales,
which may include electronic versions and/or custom covers and content particular to your business, training goals,
marketing focus, and branding interests. For more information, please contact:
U.S. Corporate and Government Sales
[phone]
[email protected]
For sales outside the United States please contact:
International Sales
[email protected]
Visit us on the Web: informit.com/aw
Library of Congress Cataloging-in-Publication Data
Johnson, Eric, 1978–
A developer’s guide to data modeling for SQL server : covering SQL server
2005 and 2008 / Eric Johnson and Joshua Jones. — 1st ed.
p. cm.
Includes index.
ISBN 978-0-321-49764-2 (pbk. : alk. paper)
1. SQL server. 2. Database design. 3. Data structures (Computer science)
I. Jones, Joshua, 1975- II. Title.
QA76.9.D26J65 2008
005.75'85—dc22 2008016668
Copyright©2008 Pearson Education, Inc.
All rights reserved. Printed in the United States of America. This publication is protected by copyright, and permis-
sion must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or trans-
mission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise. For information
regarding permissions, write to:
Pearson Education, Inc.
Rights and Contracts Department
501 Boylston Street, Suite 900
Boston, MA 02116
Fax [phone]
ISBN-13: 978-0-321-49764-2
ISBN-10: 0-321-49764-3
Text printed in the United States on recycled paper at Courier in Stoughton, Massachusetts.
First printing, June 2008
C
ONTENTS
Preface xv
Acknowledgments xvii
About the Authors xix
PART I Data Modeling Theory. . . . . . . . . . . . . . . . . . . . . . . 1
Chapter 1 Data Modeling Overview. . . . . . . . . . . . . . . . . . . . . . . . . 3
Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Relational Database Management Systems. . . . . . . . . . . . . . . . . . . . . . . 5
Why a Sound Data Model Is Important . . . . . . . . . . . . . . . . . . . . . . . . 6
Data Consistency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Scalability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Meeting Business Requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Easy Data Retrieval. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Performance Tuning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
The Process of Data Modeling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Modeling Theory. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Business Requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Building the Logical Model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Building the Physical Model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Chapter 2 Elements Used in Logical Data Models . . . . . . . . . . . . . . 23
Entities. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Primary and Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Domains. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Single-Valued and Multivalued Attributes. . . . . . . . . . . . . . . . . . . . . . . 32
Referential Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
ix
x Contents
Relationships. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Relationship Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Relationship Options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Cardinality. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Using Subtypes and Supertypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Supertypes and Subtypes Defined. . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
When to Use Subtype Clusters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Chapter 3 Physical Elements of Data Models . . . . . . . . . . . . . . . . . 45
Physical Storage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Referential Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Primary Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
Implementing Referential Integrity. . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
Programming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Stored Procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
User-Defined Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
Triggers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
CLR Integration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Implementing Supertypes and Subtypes . . . . . . . . . . . . . . . . . . . . . . . 75
Supertype Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Subtype Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Supertype and Subtype Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Supertypes and Subtypes: AFinal Word. . . . . . . . . . . . . . . . . . . . . . . 79
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Chapter 4 Normalizing a Data Model. . . . . . . . . . . . . . . . . . . . . . . 81
What Is Normalization?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Normal Forms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Determining Normal Forms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Denormalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
Contents xi
PART II Business Requirements . . . . . . . . . . . . . . . . . . . . . 95
Chapter 5 Requirements Gathering . . . . . . . . . . . . . . . . . . . . . . . . 97
Requirements Gathering Overview . . . . . . . . . . . . . . . . . . . . . . . . . . 98
Gathering Requirements Step by Step . . . . . . . . . . . . . . . . . . . . . . . . 98
Conducting Interviews. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
Observation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Previous Processes and Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Use Cases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
Business Needs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Balancing Technical Limitations with Business Needs . . . . . . . . . . . . . 112
Gathering Usage Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
Reads versus Writes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Data Storage Requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
Transaction Requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
Chapter 6 Interpreting Requirements . . . . . . . . . . . . . . . . . . . . . . 117
Mountain View Music . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
Compiling Requirements Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Identifying Useful Information. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Identifying Superfluous Information. . . . . . . . . . . . . . . . . . . . . . . . . . 120
Determining Model Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Interpreting User Interviews and Statements. . . . . . . . . . . . . . . . . . . . 121
Interpreting Flowcharts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
Interpreting Legacy Systems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
Interpreting Use Cases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Determining Attributes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
Determining Business Rules. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
Determining the Business Rules. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
Cardinality. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
Data Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
Requirements Documentation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Entity List. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Attribute List. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Relationship List. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Business Rules List. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
xii Contents
Looking Ahead: The Business Review. . . . . . . . . . . . . . . . . . . . . . . . 143
Design Documentation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
PART III Creating the Logical Model . . . . . . . . . . . . . . . . . 147
Chapter 7 Creating the Logical Model. . . . . . . . . . . . . . . . . . . . . . 149
Diagramming a Data Model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Suggested Naming Guidelines. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Notations Standards. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Modeling Tool. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Using Requirements to Build the Model . . . . . . . . . . . . . . . . . . . . . . 157
Entity List. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Attribute List. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161
Relationships Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
Business Rules. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
Building the Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164
Entities. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
Primary Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
Relationships. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
Domains. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
Attributes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170
Chapter 8 Common Data Modeling Problems. . . . . . . . . . . . . . . . 171
Entity Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Too Few Entities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Too Many Entities. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Attribute Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
Single Attributes Contain Different Data. . . . . . . . . . . . . . . . . . . . . . . 176
Incorrect Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
Relationship Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
One-to-One Relationships. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
Many-to-Many Relationships. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
Contents xiii
PART IV Creating the Physical Model . . . . . . . . . . . . . . . . 187
Chapter 9 Creating the Physical Model with SQL Server. . . . . . . . 189
Naming Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
General Naming Guidelines. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
Naming Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
Naming Columns. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Naming Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Naming Stored Procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
Naming User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
Naming Triggers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
Naming Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
Naming User-Defined Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . 197
Naming Primary Keys and Foreign Keys. . . . . . . . . . . . . . . . . . . . . . 197
Naming Constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
Deriving the Physical Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
Using Entities to Model Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
Using Relationships to Model Keys. . . . . . . . . . . . . . . . . . . . . . . . . . 209
Using Attributes to Model Columns. . . . . . . . . . . . . . . . . . . . . . . . . . 210
Implementing Business Rules in the Physical Model . . . . . . . . . . . . . . 211
Using Constraints to Implement Business Rules . . . . . . . . . . . . . . . . . . 211
Using Triggers to Implement Business Rules. . . . . . . . . . . . . . . . . . . . . 213
Implementing Advanced Cardinality . . . . . . . . . . . . . . . . . . . . . . . . . 217
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
Chapter 10 Indexing Considerations . . . . . . . . . . . . . . . . . . . . . . . 221
Indexing Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221
What Are Indexes?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224
Database Usage Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
Reads versus Writes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
Transaction Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232
Determining the Appropriate Indexes. . . . . . . . . . . . . . . . . . . . . . . . 233
Reviewing Data Access Patterns. . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Balancing Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Covering Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234
xiv Contents
Index Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
Index Maintenance Considerations. . . . . . . . . . . . . . . . . . . . . . . . . . 235
Implementing Indexes in SQL Server . . . . . . . . . . . . . . . . . . . . . . . . 236
Naming Guidelines. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
Creating Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
Filegroups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
Setting Up Index Maintenance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
Chapter 11 Creating an Abstraction Layer in SQL Server . . . . . . . . 241
What Is an Abstraction Layer? . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
Why Use an Abstraction Layer? . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Security. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Extensibility and Flexibility. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
An Abstraction Layer’s Relationship to the Logical Model. . . . . . . . . . 245
An Abstraction Layer’s Relationship to Object-Oriented
Programming. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
Implementing an Abstraction Layer . . . . . . . . . . . . . . . . . . . . . . . . . 247
Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248
Stored Procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250
Other Components of an Abstraction Layer . . . . . . . . . . . . . . . . . . . . 254
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254
Appendix A Sample Logical Model . . . . . . . . . . . . . . . . . . . . . . . . . 255
Appendix B Sample Physical Model . . . . . . . . . . . . . . . . . . . . . . . . 261
Appendix C SQL Server 2008 Reserved Words . . . . . . . . . . . . . . . . 267
Appendix D Recommended Naming Standards. . . . . . . . . . . . . . . . 269
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271
P
REFACE
As database professionals, we are frequently asked to come into existing
environments and “fix” existing databases. This is usually because of per-
formance problems that application developers and users have uncovered
over the lifetime of a given application. Inevitably, the expectation is that
we can work some magic database voodoo and the performance problems
will go away. Unfortunately, as most of you already know, the problem
often lies within the design of the database. We often spend hours in meet-
ings trying to justify the cost of redesigning an entire database in order to
support the actual requirements of the application as well as the perform-
ance needs of the business. We often find ourselves tempering good design
with real-world problems such as budget, resources, and business needs
that simply don’t allow for the time needed to completely resolve all the is-
sues in a poorly designed database.
What happens when you find yourself in the position of having to re-
design an existing database or, better yet, having to design a new database
from the ground up? You know there are rules to follow, along with best
practices that can help guide you to a scalable, functional design. If you
follow these rules you won’t leave database developers and DBAs curs-
ing your name three years from now (well, no more than necessary).
Additionally, with the advent of enterprise-level relational database man-
agement systems, it’s equally important to understand the ins and outs of
the database platform your design will be implemented on.
There were two reasons we decided to write this book, a reference for
everyone out there who needs to design or rework a data model that will
eventually sit on Microsoft SQL Server. First, even though there are
dozens of great books that cover relational database design from top to bot-
tom, and dozens of books on how to performance-tune and write T-SQL
for SQL Server, there wasn’t anything to help a developer or designer
cover the process from beginning to end with the right mix of theory and
practical experience. Second, we’d seen literally hundreds of poorly de-
signed databases left behind by people who had neither the background in
xv
xvi preface
database theory nor the experience with SQL Server to design an effective
data model. Sometimes, those databases were well designed for the tech-
nology they were implemented on; then they were simply copied and
pasted (for lack of a more accurate term) onto SQL Server, often with dis-
astrous results. We thought that a book that discussed design for SQL
Server would be helpful for those people redesigning an existing database
to be migrated from another platform to SQL Server.
We’ve all read that software design, and relational database design in
particular, should be platform agnostic. We do not necessarily disagree
with that outlook. However, it is important to understand which RDBMS
will be hosting your design, because that can affect the capabilities you can
plan for and the weaknesses you may need to account for in your design.
Additionally, with the introduction of SQL Server 2005, Microsoft has im-
plemented quite a bit of technology that extends the capabilities of SQL
Server beyond simple database hosting. Although we don’t cover every
piece of extended functionality (otherwise, you would need a crane to carry
this book), we reference it where appropriate to give you the opportunity
to learn how this functionality can help you.
Within the pages of this book, we hope you’ll find everything you need
to help you through the entire design and development process—every-
thing from talking to users, designing use cases, and developing your data
model to implementing that model and ensuring it has solid performance
characteristics. When possible, we’ve provided examples that we hope will
be useful and applicable to you in one way or another. After spending
hours developing the background and requirements for our fictitious com-
pany, we have been thinking about starting our own music business. And
let’s face it—reading line after line of text about the various uses for a var-
char data type can’t always be thrilling, so we’ve tried to add some anec-
dotes, a few jokes, and even a paraphrased movie quote or two to keep it
lively.
Writing this book has also been an adventure for both of us, in learn-
ing how the publishing process works, learning the finer details of writing
for a mass audience, and learning that even though we are our own worst
critics, it’s hard to hear criticism from your friends, even if they’re right;
but you’re always glad that they are.