Identity fields - Why use them

I am building a database for a new project. I am not a DBA or a database designer so please bear with me on this question.

My boss believes that the only time to use an identity column is when we cannot determine a unique primary key. On tables where we can determine a unique primary key identify fields are a waste of reasources. For instance, one of the tables that I need to create is a customer table. Since all of our customers have unique customer numbers my boss believes that in this case an identity column is useless. I don't have enough experience to determine if he is correct or not.

Can someone please explain the pro's and con's of using identity fields to me.

Thanks

Check if any of these help:http://www.sqlteam.com/item.asp?ItemID=2599

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=5113

|||

NewToDotNet:

My boss believes that the only time to use an identity column is when we cannot determine a unique primary key. On tables where we can determine a unique primary key identify fields are a waste of reasources. For instance, one of the tables that I need to create is a customer table. Since all of our customers have unique customer numbers my boss believes that in this case an identity column is useless.

I agree with your boss, but I know some people would disagree. It's like religion or politics, there's no point arguing about it.

You need some kind of unique reference for each record, or else you say update that record right there, and accidentally update another thousand that have the same surname and forename.

So if I just had a person's name, I might have several different John Smiths, and so I would want to generate an identity. If I had a real-world unique reference, like a social security number, and could rely on each person to supply it accurately, I would not create another "fictitious" Id.

Unless I was collecting names at a fast food restaurant, where I could not rely on each customer accurately supplying a social security number, so I would have to generate something.

Or unless I had to handle foreign tourists, who would not have a social security number.

Or unless maybe I had databases in different branches, and there was the chance that Joe Public with social security number xxx had done business with more than one branch.

So you have to stop and think how confident you are about the reliability of your "real world" reference, but if you have something dependable, use it.

|||

The issue here is whether one should use a "dataless" key or not. A dataless key is a key that has no intrinsic meaning, it's just a way to uniquely identify a row. Many data modelers believe that dataless keys should be used in all cases because they solve the problem of what happens when your keys domain changs (eg, you discover after 5 years that the key field you thought was big enough isn't and you have to make your item number a 12 character number instead of an 8 character field). This happens more than you think. Here's a nice article describing the issue:http://blogs.ittoolbox.com/eai/implementation/archives/physical-db-design-key-fields-15666. There's a lot of literature on this topic in the database/data modeling world.

OTOH, programmers hate dataless keys for obvious reasons. They are unintuitive, don't lead you "naturally" to join conditions, and make more work up front.

Personally, I hate dataless keys but concede that they are probably a better way to go.

I would bring the issue of dataless keys up with your boss to show him that you know more than he does but in the end go with what he wants -- 'cause he writes your review.

|||

I want to thank everyone who responded to my question. Based on the posts and articles that were presented I see that there are many opposing opionions on this subject, so I can't really say my boss is wrong. And as dbland07666 says, my boss does my review.

Thanks

|||

Your boss may be correct.

If you can find a natural primary key that is reasonable in size, is precise, guarantees uniqueness, will never change, and can never change, then using that as a primary key is ok.

Unfortunately, finding a primary key like that is harder than you may think. If you can't find one that has all the above attributes, then you may still be able to use it as a primary key with some drawbacks. Whether the drawbacks are worth it is a matter of opinion, and as you said, the only real opinion that counts is the one that writes your review.

|||

Thanks for the additional input. I appreciate it.