In this last kind of “creative” chapter, I will look at some of the ways you implement common problems in your relational database, and some of the ways you probably shouldn’t. The “should” sections will deal with:

• Uniqueness – Beyond the simple uniqueness we have covered in the first chapters of the book, looking at some very realistic patterns of solutions that cannot be implemented with a simple uniqueness constraint.
• Data Driven Design – The goal of data driven design is that you never hard code values in your code that don’t have a fixed meaning. You break down your programming needs into situations that can be based on sets of data values that can be modified without affecting code.
• Hierarchies – A very common need is to implement hierarchies in your data. The most common example is the manager-employee relationship. In this section I will demonstrate the two simplest cases, and summarize other methods that you can explore
• Images, Documents, and Other Files – There are quite often a need to store documents in the database, like for a web users’ avatar picture, or a security photo to identify an employee, or even documents of many types. We will look at some of the methods available to you in SQL Server.
• Storing User-Specified Data – You can’t always design a database to cover every known future need. In this section I will cover some of the possibilities for letting the user extend their database themselves in a manner that can be somewhat controlled by the administrators.
• Generalization – In this section we will look at some ways that you will need to be careful with how specific you make your tables so that you fit the solution to the needs of the user.

This marks an increase of a 4 sections from the last book, when I added this Patterns chapter.  I did take away a few bits about sequence and calendar tables, but I do plan to move this to a later chapter on development, where I will discuss the sorts of objects that I find nice to have in each database and why.

For the anti-patterns, I am adding one more in this edition, on undecipherable data.

• Undecipherable data – Too often you find the value 1 in a column with no idea what 1 means without looking into copious amounts of code.
• One-size-fits-all domain – One domain table to implement all domains rather than individual tables that are smaller and more precise
• Generic key references – Having one column where the data in the column might be the key from any number of tables, requiring you to decode the value rather than know what it is.
• Overusing unstructured data – The bain of existances of the dba, the blob of text column that the  user swears they put well structured data in for you to parse out. Can’t eliminate a column for notes here and there, but overuse of such constructs lead to lots of dba pain.

This is one of my favorite chapters because it really gets to the core of design. Up until now we have stuck mostly to basics and fundamentals, building very basic structures and working with the object types available to us. Here we build practical solutions to solve common problems. 

From here, the next chapter we will move along to security, structures, and then putting the finishing touches on things. Hopefully soon I will have some exciting new about a final chapter that will tie it all together once I get final approval and acceptance from the guest writer on this chapter.