Standardizing Primary keys

Question

Standardizing Primary keys

fadhiliss samone

SSC Veteran

Points: 286
More actions
July 16, 2007 at 3:17 am

#68860

I have already designed a database in SQL Server 2005.But I just want some advices on better way of standardizing primary key IDs/Code.This single table would keep records for Public Entities/bodies ie Ministries,Local Government Authorities and Agencies.Should I use the uniform format for all Public Entities' ie PECode like (with a PE prefix followed by a number sequentially ie PExxx eg PE001,PE002....PE500) or prefix each category accordingly eg Ministries ME001,ME002...etc : Local Government Authorities LGA001,LGA002...etc and Agencies AE001,AE002,....etc..........Which is the best practice????
Please help urgently!
Thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

I have always gone with my way og naming conventions but hve the habit of documneting them and putting it under a place accessible to all. By that way you can even let know the DBA who takes after you that things are already in place.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 2

No... I wouldn't make an alpha-numeric primary key... they're slow and difficult to maintain.

Why don't you just use an INDENTITY column and a Country column?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

Hi

As Jeff said have a integer identity column. have another column which tells which "type" of body it is. even this column can be numeric.

ex:- 1 = Ministries etc...

just document it correctly.

"Keep Trying"

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 4

I wouldn't make an alpha-numeric primary key... they're slow and difficult to maintain.
Why don't you just use an INDENTITY column and a Country column?

Alpha or alpha-numeric keys are not significantly slower than numbers, certainly not so much that you would need to dump a perfectly good alpha natural key field in favor of a numeric identity field. However, if you generate the value by taking some alpha prefix and appending a value, then that certainly would be difficult to maintain.

If you feel that the entities are sufficiently different from one another that they warrant a different sequence of key values, then they are probably sufficiently different to warrant placing in separate tables. If, however, the concept Public Entity is sufficiently broad to include "Ministries, Local Government Authorities and Agencies," then trying to get fancy with key values is pointless and counter-productive.

Examine the attributes of the entities you will be storing. Is there already an attribute or group of attributes that uniquely identify each entity? If so, that is your natural key. Don't be afraid of composite keys (keys made up of more than one attribute). Despite what everyone seems to be saying these days, composite keys are actually no more difficult to work with than single field keys and in many cases can actually improve the performance of queries.

However, if you do decide to go with an identity field, you still must correctly identify the natural key of the entity. Define the field(s) as NOT NULL and define a unique constraint on it(them). The casual use of identity fields in the last few years has meant that this step (according to my observations) is skipped more often than not, leading to many and powerful headaches at some point in the future.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 5

Tomm Carr (10/9/2007)
Alpha or alpha-numeric keys are not significantly slower than numbers, ...

You certainly did take that one out of context...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bob Fazio SSChampion Points: 10794 More actions · Answer 6

All of the suggestions to this point are good, except I have to also agree that yours is not.

I have no issues with Alpha or numeric keys if they fit. But don't append numbers to the key. Make it a concatenated key at least.