States Table & PK

[mastromb]

In my DB I have a states table, tblStates, which has three fields StateID, StateAbbreviation, & State Name with StateID being the PK/FK in multiple other tables.



Is there a reason I shouldn't eliminate the StateID field and the StateAbbreviation field as the PK instead? Obviously all of the states abbreviations are unique in the US but I don't know about other countries where there may be duplication of the abbreviation, even though we do little to no business with companies outside the US.

[Rainlover]

A very good question.

I am going to assume that you currently have the StateID field as AutoNumber.

This would be my preference, however having the StateAbbrevation as the Primary key will still work.

What I do not like is having a composite Primary Key.

I personally make it a rule that every Table has a Primary key of AutoNumber. It is so much easier to manage in the long run. But in therory there is nothing wrong with using a Natural Primary Key rather than a Surrogate Primary Key such as Autonumber.

My best advice is to always use Autonumber. Time will show the benefits.

[mastromb]

That was my first thought by having the StateID is that it may be valuable down the line but it seems at times that I default to complex solutions to simple problems. Thanks for your input.

[Rainlover]

Hope I helped.

[RuralGuy]

I agree with Rain for the same reasons.

[Rainlover]

RG

There are too many myths in forums such as this.

Like "Never store a Calculated Value" or "You can't use Name as a Field Name".

While these are ill advised they are still acceptable if you know how to handle them.

I try not to push too hard my preferences, but I have been guilty of doing so in the past.

[RuralGuy]

I'm choose to push people toward good habits for expedience sake. As you say those problems can be delt with but I save that as a last resort. I do try not to say *never* however.

[Rainlover]

Never say "Never"

[mastromb]

Thanks you guys for your help and patience with this and other questions. I appreciate your knowledge and guidance. I look forward to a time when I can contribute to solutions instead of just asking questions.