THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

What is a physical database?

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:

  1. The layer of tables, constraints, indexes, etc used to store data
  2. 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 (

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?"

Published Thursday, June 11, 2009 6:26 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



joemcp54 said:

Hey, it made me think.  I recently received the first of 3, I hope MCITP to be a SQLDBA.  I will have to go back and analyze how I approach my thinking on development and issue resolution.

June 12, 2009 8:12 AM

Michael K. Campbell said:

Definitely a question of semantics. I personally tend to think of it in terms of definition 1 as you point out above - even though I'm quite aware of the fact that PLENTY of people see that as being wrong.

But those people typically tend to be only DBAs or spend too much time down at the storage level in general. YES, they're correct that logical database is the relationships, structures, and so on that represents their 'application'.

But if you're an application developer, you typically don't tend to think at that level. You tend to think of the tables and other objects as being the 'physical' database (most developers could care less about the actual storage of the zeros and ones that make up their databases), and views, sprocs, and other persistance details as being the 'logical' database.

Since I actually wear both hats (DBA and Developer) the way I try to stay out of trouble is by using the word 'schema' at this point  when discussing the subject this blog post is about. In other words, I tend to speak of 'physical schema' and 'logical schema'... only, while that tends to work well in my mind and with the right audiences... it also runs afoul of the fact that at the database level, schema means something totally different as well...

Again... semantics ;)

June 12, 2009 4:39 PM

drsql said:

Michael, I think your reply sort of makes my point stronger. Having multiple meanings for words depending on the hat you are wearing (and the conversation you happen to be in) is a really terrible thing. I mean, sure you can't expect that if you are talking to your elderly grandmother that when you say row, column, and table that she will immediately thing of the relational implementation of data storage and not something the way you plant corn, some form of fancy architecture and where you eat dinner.

But amongst database professionals, if we could get on the same page, it would be so much easier when you say the "Production DBA team only manages the physical layer" to not get 10 different views of what that means. In my venacular, this means that the Prod DBA can add indexes (not unique indexes unless the keys are supersets of other unique indexes), change partitioning strategy, move the files to a different array, etc, but they could not change a stored procedure, add a column, etc.

The data access layer (procedures, or some other layer built by the app team if the "better" way to implement an access layer has lost out :) might be accessible to them, but still it would likely be considered that they can only make changes in a lossless manner (you know, tweak a query or something like that.)

I certainly don't like to mix the data access code with relational objects. I don't mind developers writing procedures, functions, etc, but when a non-architect starts building tables, time constraints never allow the code review process to make such deep changes to the relational objects. Too many times I have had the conversation with a manager of mine "Yes, I know that name sucks. Changing it would be too costly though. Maybe in the next iteration we will fix that"

Turns out that the devil's house doesn't freeze over and this never actually takes place, so we end up with more and more comments in the data model "Note: this name needs changing. TreeSize should really have been TireSize, but they just left it."

Sometimes you go back and change it in the data access code, but usually if programmers have the rights to build their own tables, you probably have lost the battle on procedures too.

I have digressed a bit, haven't I...

June 13, 2009 1:24 PM

Charles Kincaid said:

Very good.  I can just imagine the following conversation:

Bob:  "The whole application has failed.  We can't even log in now.  What made you thinks that merging 6 tables into one would not foul things up?"

Tom: "Weel according to Cood Rule 9 ..."

... and then the fight started.

June 13, 2009 2:50 PM

Leave a Comment


This Blog


Links to my other sites


Privacy Statement