SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Review: SQL Server 2000 Database Design

By Andy Warren,

This book turned out to be far more interesting that I expected! The authors stated goal is to  write a book that falls in the middle ground between those books that focus on the nuts and bolts  of running SQL Server and the classic books on relational design by Date and Codd. Before I tell  you more about what I thought of the book, take a look at the chapter titles: 

Chapter 1 Introduction to Database Methodologies
Chapter 2 Gathering Information for a Database Project
Chapter 3 Fundamental Database Concepts
Chapter 4 Entities, Attributes, Relationships, and Business Rules
Chapter 5 Data Modeling
Chapter 6 Normalization Techniques
Chapter 7 Advanced Normalization Techniques
Chapter 8 Ending the Logical Design Phase
Chapter 9 Planning the Physical Architecture
Chapter 10 Planning and Implementing the Basic Physical Architecture
Chapter 11 Ensuring Data Integrity
Chapter 12 Advanced Data Access and Modification Techniques
Chapter 13 Determining Hardware Requirements
Chapter 14 Completing the Project

As you can see, the book is just about evenly split between logical design and physical design. I  think that is a pretty good division. The early chapters do a good job of describing relational  theory - worth reading even if you've using SQL for a while. Included here is a discussion of  nulls and the use of artificial keys and how they impact your design - a good sign to me, since  purists often scoff at both!

One of the first things to really catch my attention was the authors discussion of the different  modeling methodologies, UML and IDEF1X, in Chapter 5. It really makes you see that there is a  difference between logical and physical modeling. Most of us go through a design phase, but do  you know how to use the notation? I'll grant you that for small projects it may be overkill, but  I think it has a lot of merit for larger projects. More importantly, even if you don't use it  now, your next project or employer may. A basic grounding in this area is worth having.

Chapter 7 will take you by surprise. The author believes that third normal formal is the minimum amount of normalization you should do and states that fourth normal form is so much better, once you try it you won't go back! I won't say I'm entirely convinced, but it did make me rethink my  views:-)  Keep in mind that the first half of the book is about logical design. Most of us look at a highly  normalized design and immediately begin thinking of how to denormalize portions of it because "it  won't perform". The author makes the point that with today's hardware we can now consider  solutions that in the past might not have been workable. I totally agree with that point - SQL  Server is an amazing product, if you can normalize and keep your performance, why do anything  else? The other point made is that you're doing a logical design, not the final design. Once you  move into the physical design phase you're going to find flaws in your design, make compromises  for the sake of performance or to reduce coding time, etc.

The second half of the book will appeal to your practical side. Beginning with Chapter 9, it  focuses on how you will implement your design in the real world. All the basic features and  capabilities of SQL Server are discussed - after all, to implement your design you have to have a  good understanding of what the database can do.

Chapter 10 walks through the process of converting the logical model to a physical model.  Starting with a review of SQL data types, it covers locking, indexes, collation, foreign keys,  and cascading updates & deletes. It also talks about the differences in the physical model -  relationships you may simplify to reduce the coding involved and using physical only columns such  as a timestamp. Once nice idea I picked up in this chapter was using extended properties of SQL  objects to store things like input masks that can be used by the client, essentially a handy way  to store table level metadata.

Chapter 11 looks at how to maintain the integrity of your data using SQL Server 2000 features.  You find a brief but fair overview of how to use triggers, constraints, and stored procedures to  do this. It's not designed to teach you how to write them, but how and when to use them, though  there is enough sample code provided that even an inexperienced user could follow along and see  how the code works. Chapter 13 discusses hardware requirements, plus gives some decent coverage  on how to monitor performance.

This book covers a lot of ground in 600 pages. If you're a really experienced designer or  programmer, you won't find anything revolutionary in this book. Beginners would definitely  benefit from reading this, it's far more understandable than the classic academic texts. I think  mid level programmers/designers/DBA's will definitely see some value as well - the ones who  understand basic database theory and are familiar with SQL Server. Going into this book with a  good understanding of the technical side will let you focus on and really think about the logical  concepts the author presents.

It's a good book, I'll give it a 5. Wrox has a sample chapter online you can review.  SQLServerCentral.com will be giving away the review copy of this book (provided by Wrox) to one  our our readers in July.

Total article views: 4528 | Views in the last 30 days: 2
Related Articles

Difference between Physical & Logical instances

Difference between Physical & Logical instances


Understanding Data Normalization

Normalization, which is the key part of the OLTP databases logical design process, is a design requi...


Physical and Logical Ordering of an index

Physical and Logical Ordering of an index


Stairway to Database Design Level 9: Normalization

In the final step of Database Design, Joe Celko gives a simple but effective explanation of the norm...


Whitepaper: SQL Server 2005 Physical Database Storage Design

Microsoft has released a whitepaper on SQL Server 2005 Physical Database Storage Design (Microsoft ...

book reviews