Or “Finally getting to use SQL Server like the dang name of the book implies”

I am smack dab in the middle of chapter 6 now, and I have finally figured out the concept and how it is going to be. Originally, chapter 6 was going to be the start of a series of design pattern chapters, but a funny thing happened when I reached the end of Chapter 5 on Normalization. The book felt incomplete. My feeling had been to assume that the reader had used SQL, knew how to execute a create table statement, an insert statement, etc. I am still going to do that, and I am trimming down this chapter from 96 pages in the previous version to 60 or 70 pages, but it still had to be done.

I am taking a small database that I designed (a conference messaging database, twitterish, but not exactly. An alterative idea that I got from a RDW2 was a menu/ingredients, but to include a time element seemed too difficult) that has 5 tables to start with and 6 when it is all said and done. Keeping it small means all of the DDL can be printed in the book.

The structure of the chapter is broken into two sections. The first section is preparing the model for implementation.  The model is a logical model that is generally implementation non-specific, fully normalized, and could be built with any sort of key structure.  The job is to transform it into a fully implementable SQL Server model, using whatever your standard primary key structure might be (I use surrogates with a required unique constraint for my tables). The reality of how we all really work is that most of these topics are done as we go along, but not always do we get to do the modeling from start to finish. When I end up implementing someone else’s design, I will usually normalize their tables if needed, then apply the following steps to standardize the model to whatever standards are in place.

This main section would be named something like “Adapting the Logical Model”

• Choosing names: I’ll mention naming concerns for tables and columns. The biggest thing here is making sure to have a standard and to follow it.
• Choosing key implementation: Throughout the earlier bits of the book, you’ve made several types of key choices. In this section, you will go ahead and add all the keys to the model.
• Determining domain implementation: I’ll cover the method to decide datatypes, nullability, and, also important, choosing between using a domain table or a column with a constraint for types of values where you want to have solid constraints on column values.
• Setting up schemas: Beginning in SQL Server 2005, you could set up groups of tables as schemas that provide groupings of tables for usage, as well as security.
• Adding Implementation Columns: There are columns that are common to almost every database that people implement that are not part of the logical design. For example the last time the row was updated, and by whom it was updated.

The next major section will be “Using DDL to Create the Database” In this section, I will go through the common DDL that is needed to build most every database you will encounter. Basically, using the model I have created, I will go through the process of creating a very basic database. For non-trivial utilizations, we will demonstrate them as patterns that can be followed to do certain tasks. If it feels goofy to cover that way, I just won’t include it…

The second section of the chapter will cover the following sections:

• Creating the basic table structures: Building the base objects with columns.
• Adding uniqueness constraints: Using primary and unique constraints to enforce uniqueness between rows in the table.
• Using default constraints: Assisting users in choosing proper values when it isn’t obvious.
• Adding relationships: Defining how tables relate to one another (foreign keys), including cascading options
• Documenting the database: Including documentation directly in the SQL Server object using extended properties.
• Validating the dependency information: Using the catalog views and dynamic management views, you can validate that the objects you expect to depend on the existence of one another do in fact exist, keeping your database cleaner to manage.

In some ways if feels like 2 chapters, and it is going to be pretty long, but I like that the chapter takes a model with logical terminology/domains and ends up a database in SQL Server. Which is what people are going to want out of the book anyhow.  After this chapter I won’t do complete databases anymore (first off because they are a pain in the hiney to keep straight over 70 pages, because any mistake or forgotten concept means having to go back to when the snowball was really small and make sure everything is in there already.  So we will stick to independent model snippets for the rest of the book.