Louis Davidson : Database Design

24 Hours of PASS next week, pre-con preview style

drsql — Fri, 14 Sep 2012 00:53:47 GMT

I will be doing my Characteristics of a Great Relational Database , which is a session that I haven’t done since last PASS. When I was asked about doing this Summit Preview version of 24 hours of PASS, I decided that I would do this session, largely because...(read more)

And interview, an online session, a long drive and a SQL Saturday… This week!

drsql — Tue, 14 Aug 2012 04:38:03 GMT

Later this week I will be doing an episode of the Greg Low’s excellent SQL Down Under podcast ( http://www.sqldownunder.com/Resources/Podcast.aspx ), something I did once before back in 2006. If you haven’t listened to any of the previous editions,...(read more)

PASS Week/Speaking/Doing Schedule

drsql — Mon, 10 Oct 2011 23:20:08 GMT

Well, we are finally here at what is the secular version of the holiday season for Microsoft SQL Server nerd types, the week of the SQLPASS Summit. This year, I am speaking 3 times and will also be doing the Quiz Bowl at the Welcome Reception, so I am...(read more)

Chapter 8–Patterns and Anti-Patterns

drsql — Sun, 10 Jul 2011 17:29:42 GMT

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...(read more)

Chapter 7–Enforced Data Protection

drsql — Tue, 21 Jun 2011 04:36:13 GMT

As the book progresses, I find myself veering from the original stated outline quite a bit, because as I teach about this more (and I am teaching a daylong db design class in August at http://www.sqlsolstice.com/ … shameless plug, but it is on topic :)...(read more)

See you in Columbus Saturday?

drsql — Mon, 06 Jun 2011 04:32:45 GMT

Assuming all goes as planned, I will be in Columbus, OH this Friday night and Saturday for SQL Saturday 75 . I really love SQL Saturday events the best of all of the events because they are very intimate in nature. As a fairly antisocial person, I sometimes...(read more)

Normalization and How to Know When You Are Done… The short version…

drsql — Sun, 29 May 2011 20:54:15 GMT

A while back, I was working on a short article about Normalization for a book that never got published (admittedly I wasn’t getting paid for the article, and it wasn’t for charity, so I wasn’t that broken up over it.) The task at hand was to, in...(read more)

Chapters Two, Three, and Four

drsql — Wed, 23 Feb 2011 04:21:39 GMT

I am trying to blog all of the chapters of the book, but due to deadlines and a lot of shuffling about, I never got around it for these three chapters, two of which I have added since I wrote the original table of contents. All of these contain mostly...(read more)

Design Book–Dimensional or No Dimensional, that is..the question

drsql — Wed, 01 Dec 2010 02:29:31 GMT

So, it is right there in the title of the book “Relational Database Design” etc (the title is kinda long :) But as I consider what to cover and, conversely, what not to cover, dimensional design inevitably pops up. So I am considering including...(read more)

Design Book– First Section (Skills)

drsql — Tue, 23 Nov 2010 03:04:23 GMT

One of the main things that I haven’t always loved about the previous books is that it wasn’t a perfect reference book. I focused on having a flow throughout the book that, not unlike a school class, started at the beginning and finished at the end. Interspersed...(read more)

Design Book–Top level outline

drsql — Wed, 17 Nov 2010 04:10:12 GMT

The more I teach sessions about database design, the more I realize that two things are true. First, most people don’t dig the normalization stuff as much as I do (some do), and second, people really need the normalization stuff more than they think....(read more)

Seventh pillar - Encapsulated

drsql — Fri, 23 Oct 2009 21:50:52 GMT

The final numbered post in this version of my “pillar” series of posts ends in the most contestable part of the design/implementation process. Encapsulation. The concept of encapsulation is not contested (or even contestable by sane programmers...(read more)

Fifth pillar - Secure

drsql — Tue, 06 Oct 2009 23:52:42 GMT

As I have mentioned in all of the previous posts, basic functionality is the foundation of any system. So it goes without saying that if you have just implemented a payroll system, everyone is getting paid. To meet the basic bar that EVERYONE agrees...(read more)

What is a physical database?

drsql — Thu, 11 Jun 2009 22:26:36 GMT

A bit of terminology that gets beaten to death is that of the “physical” database. I would think most every DBA uses this term (I do), but…to mean what? I think there are two common utilizations:

The layer of tables, constraints, indexes, etc used to store data
The actual on-disk structures.

Frankly, until 3 years ago, I used the first interpretation. However, I was beaten up pretty badly by a few people whom I don’t really remember (I think Anith Sen was one of them.) The problem is, I was scolded, “physical” already had a meaning, given it by the “founder” himself, EF Codd.

So, checking his 12 Rules, Codd stated the following two things:

Rule 8: Physical data independence:

Changes to the physical level (how the data is stored, whether in arrays or linked lists etc.) must not require a change to an application based on the structure.

Rule 9: Logical data independence:

Changes to the logical level (tables, columns, rows, and so on) must not require a change to an application based on the structure. Logical data independence is more difficult to achieve than physical data independence.

And actually, the implementation layer really is the logical model if you follow his terminology since his rules were pertaining to the relational model and not the entire design process. This article says it better than I can in a long blog, but I am not sure about that URL (mac.com?):

http://homepage.mac.com/s_lott/iblog/architecture/C465799452/E20080301143528/

The physical layer of a relational database occurs down at the file system level. Codd's "Rule 8" (Physical Data Independence) says that the things we're designing in ERwin (and similar tools) are the things our application depends on. These are not physical in nature, but are the relational implementation.

So the thing I am trying to say is that physical means that a little 5 volt charge is sitting there representing a bit of data in the physical world. I like the term logical to mean implementation platform non-specific.. The thing in the middle is the SQL Server/Relational implementation specific model. It may take liberties to optimize for SQL Server, but it is not physical. That is were partitioning. indexing, filegroups, etc come in. Changes to this layer ought never be noticable by the application.

I guess in the comments, I ought to expect a good number of replies that might start to answer the question. Does it matter? Is it only semantics? Hey if you don’t think semantics matter, I hope that when you find yourself drowning that the person who has the choice of tossing you a life preserver or a sack of door knobs interprets the meaning of your cry for help in the way you intended. You would hate to find yourself at the bottom of a lake thinking “hmm, I wonder why they did that? Did they hate me, of just mis-interpret the meaning of my sentence?"

Checkpoint – Four pillars down, Three to Go

drsql — Thu, 14 May 2009 22:32:51 GMT

With the previous post on the fourth pillar, I have reached the “end” of the design posts. To review, these were:

Coherent – cohesive, comprehendible, standards based, names/datatypes all make sense, needs little documentation
Normal – normalized as much as possible without harming usability/performance (based on testing)
Fundamentally Sound – fundamental rules enforced such that you don’t have to check datatypes, base domains, relationships, etc
Documented – Anything that cannot be gather from the previous four is written down and/or diagrammed for others

When you are looking at a database that you have NO idea what it does, or why it exists, these things I have so far outlined will be the sorts of things you notice first. If NOT(normalized AND understandable AND data protected AND documented) = TRUE, then you will feel it and really hope that you are an hours based contractor with your highest rate and a set time to pack up and head elsewhere richer than a . If the opposite is true, being an employee for that company is where you want to be.

Looking back, one thing stands out to me as a possible (reasonable) concern. I didn’t include anything about “complete”. By the time my next edition of “Pro SQL Server 2008 Relational Database Design and Implementation” rolls around, it might. However, my initial thinking was that these were characteristics beyond the basic “it must at least work” benchmark that should be obvious to any semi-logical human being (which I suppose limits the gene pool, but at least most readers of my book who don’t misunderstand the term data model as a social activity will meet that requirement). I sort of hope it goes without saying to those folks that a database cannot be a good database without it serving the needs of the users. The only thing I am trying to point out in these pillars is to ascertain how cleanly the database does that task.

I also think that I might do some rearranging to make Normal and Sound Theory as the foundation and just have six pillars. Either way, things will change.

For example, if you were modeling a time entry system, no matter how normalized, understandable, data protected and documented your system was, if you failed to capture who was entering time and the employees never got paid, your database would suck. In fact, I would go so far as to warn you to expect a large group of people at your door with pitchforks and torches ready to give you an alternate meaning to the term “perforated colon” or at least a crash course in database design/implementation.

The thing is that anyone building a system to capture employee’s time and getting them paid would do a certain set of things, even if the entire system was done on paper. The fact is, rare is the case that a system is created that won’t minimally do the task that was originally asked for in some reasonably decent manner. In fact, this can, as a person who wants to get it done right, be your biggest enemy. Getting it done well can easily take a back seat to just getting it done. Worse still, there is some truth to the logic to this statement. As the old saying goes:

Better is the enemy of good enough
Admiral of the Fleet of the Soviet Union Sergey Georgiyevich Gorshkov

But the problem with that saying is in the interpretation. What is “good enough”? To me, this lack of a definition of good enough is what derails a lot of projects where this is the sentiment. If you don’t define “good enough” as being a solid platform for growth and usability, this is pretty much a half truth, and a half truth is far more difficult to deal with than a complete lie.

And then she understood the devilish cunning of the enemies’ plan. By mixing a little truth with it they had made their lie far stronger.
C. S. Lewis, The Last Battle

As the architect of a system, just getting it done is NOT the goal. It is a very important step along the way to the actual goal, but it is not the goal. Malleable software that can grow and expand to meet the needs of the users now and in the future is the goal that serves your customers best. The cost of creating and managing software over months and years is often a lot more than is initally thought. The cost of having a human do the work on paper and filing cabinets can be more efficient and cheaper than poorly written, hard to manage software. Too often computers just turn a bunch of paper forms into bits and bytes rather than streamlining a process to make the computer do the work for you. Sometimes it is just protecting the jobs of others that causes this, but the fact is, eliminating a job of a person who pushes a button over and over every day will allow someone to be hired to do actual work (even the button pusher!) that can add to the bottom line, not simply waste resources.

The pillars were conceived a “catch phrase” to help think about how we can ease the process of getting the system built, created, and eventually maintained in a manner that helps the eventual product be useful for years to come, and in the process help you to become a respected member of your team, not the one who is talked about as a “good riddance” once you have finally left the company.