The third section is the primary section that a person who has some decent knowledge and experience doing design will likely really find exciting. Whereas the first half of the book is there for fundamentals, this section is more skills based, and unless you are a walking encyclopedia of SQL Server syntax (and I am not), you have to use some form of reference to discover how to implement different sorts of problems using DDL, including Triggers, Constraints, etc;  Security; Source Control, etc.

Possibly using examples from the previous section’s models that were created as the basis, the chapter breakdown will look something like:

10 (perhaps > 1 chapter). DDL Solutions to Implementing Requirements – Using SQL Server DDL, present methods to solve different problems that will be faced.  For example, techniques for implementing relationships or all sorts, hierarchies, uniqueness, subtypes, cardinality enforcement, domains, etc. The goal will be to demonstrate all of the primary DDL including create table; check, default, and foreign key constraints; triggers; functions; and a smattering of stored procedure usage.

Back in the first half of the book, where we largely were uninterested in implementation, rather more interested in modeling the correct requirements, regardless of how implementable the model was, because no matter the requirement, there is always a solution, even if it is just telling the users “don’t do that”, which sounds lame but a really large amount of requirements will actually be based on such instructions.  Take a column for a person’s first name. What is supposed to go in this column? An exact, properly spelled name. What is the best we can implement at the database level? A N character string. In the UI layer, we can add to that rules such as: Name shouldn’t include numbers, Name should include vowels, Name should match gender, Name should… But should is not must. If a dad wants to name his son “Sue”, then who are we to say no?

One consideration is whether this chapter is really two chapters, one for declarative solutions, one for code solutions. The reason it is lumped into one is that I have not convinced myself of whether these sections should be organized by object or by type of problem.

11. Security – Security is more and more becoming a forgotten art. At the database level the most that is often implemented is to use group membership to determine who has access to the database, but then simply have the application do all of it’s calls from a different security context stored at the app server. This model isn’t terrible in some respects, as it obfuscates the login process such that the user never has direct access to the database. The downside is that security is managed through the app only, which means that every application does it differently. While I clearly won’t solve this problem, I will discuss a methods of implementing security using SQL Server’s internal security methods, including ways to implement row-level security and other common problems.

12. Data Quality or What to do when users do dumb stuff – Unfortunately, while we can do a lot to ensure data quality, there are many situations where data isn’t exactly perfect. In the best database, if a user spells Fred as Dumbjerk, you really can’t stop them. If a user puts in a birthdate of 19000101, is that correct? What if the person is a high school student? Sadly, these are the good cases! Worst case scenario is that you have to use data from an outside source and their architect attended the Inappropriate PASS session on database design and thought that the single table database was in fact, a good thing (some people don’t get humor).

13. Managing the database implementation lifecycle – In this (likely) short chapter, I will cover the basics of maintaining the database throughout its lifecycle along with some discussion of how best to implement the database. Of course, the first time you create the database it is easy. No important state, drop table, add new table. Bam, easy.  But after developers start creating user interfaces, and users start doing business with your structures they are no longer “your” structures. So you have to manage the process making sure you can always recreate the data that users once had, even when you have changed the structures considerably (sometimes making multiple tables out of what was a part of another table.

In the end, this seems like a pretty meaty 4 chapters and it probably is. In all of my books before (save for the DMV one), chapter separation has been a pretty large issue. Chapters were huge, 80 page things that were certainly not digestible in one sitting, even if you treated them like the technical book equivalent of Thanksgiving dinner. That problem not withstanding, the final goal will be to have a couple of hundred pages at the most where the reader can search for most any common topic and get a DDL solution (this is what I hope drives ebook sales and why I kind of prefer ebooks for technical books.  Searching them, finding what I need, and using the copy command to get the code. Code downloads just never cut it for me as you have to correlate the book to the download, and there is never any easy way to do this.