Primary keys (1 of 2)

There is a lot of debate going around the industry today regarding Primary keys and how to select the column to use. I have heard three arguments on this subject
• Guid
• Identity
• A column that represents a unique property of the entity in question

In this 2 part blog I will weigh in on my personal preference as well as discuss all three solutions
Today in part one we will discuss the goals we wish to achieve for the primary key and we will discuss the first option. Then tomorrow we will discuss the other 2 options and come to our conclusion

Let me just say now my personal preference is Guids
I have worked and seen the impact on all 3 solutions and feel this is the best one. Is it perfect? No but it comes the closest.
First let’s examine the criteria we would like in a perfect word for our primary key to have.
1. It has to be able to uniquely identify a row
2. Since in most cases the key will be clustered it should be what I call a “light weight” data type
3. It would be great if we could attached some meaning to the column we choose
4. Whatever method we choose it should be implemented though out the whole database

OK I know a lot of people are not going to agree with goal 4.
Let me explain. In my view all tables in a system should follow the same basic design for no other reason than ease of use and understanding by all developers and administrators on staff. What goes for one table should work for all other tables.
Are there exceptions? Yes but they better be good ones I want to know why and have a chance to make our implantation work.

So let’s examine the three choices

Off our three choices the one that might have a chance of obtaining all our primary goals is

Choosing a column represents a unique property of the entity in question

So obviously this method is the only one that will satisfy goal 3. The problem lies with the other 3 goals. And the time costs to everyone evolved by implementing this method

Let’s look at goal 1 again. We have to be able to identify a row with this column.
That means for every table we create we need to be able to identify a row by one column (or a combination of columns) . Has anyone actually implemented this on a whole database??? I think not it is next to imposable to do this for every table in your system
That means it will most likely violate rule 4 and quite possibly rule 2.
I have seen this happen many times people start banging their heads together trying to find that one column that will uniquely identify this entity. All of a sudden columns like email address and some weird long string representing a product number., Or my favourite some combination of 3 characters from one column and 2 characters from this column to make a unique value. ....... do I even have to comment . and yes OldBalls if you are reading this I am talking to you. Then there is the time its going to take to go through every table and design this solution the frustrations that will happen when you realize that you can’t find any column to use for x table and resort to mixing methods and then what do you have? One cluster **** of a database that’s what
In closing this is the worst method to choose and should be avoided like ketchup on a white suit.

comments, questions? drop me a line at
mccannblogs@gmail.com

McCann OUT!!!!!!!!