CODEWITHSUNDEEP
databasedatabase-design
wootscootinboogie
wootscootinboogie

Reputation: 8695

Using designer-defined IDs versus auto-gen ones

This is more a question of practical database design. I've designed smaller databases before, but nothing on the level of what I'm doing now (several million records), and I now need to consider efficiency and performance much more than I did before.

Consider the following: being given a large table with an ID# with 10 or so digits. This would of course be the primary key. From what I understand it's bad practice to store the key as an integer unless you plan on doing math on it (please correct me if I'm wrong here). Is it best practice to store the key as a nvarchar(n) where n is the string length of the key? What about making your own primary keys (say an incremental key)? The size of the key would be smaller, but does it matter enough to detract from the fact that you can import data directly into the database that already has a relationship defined? (Importing a table with a foreign key from another table. Like a state code).

Upvotes: 0

Views: 49

Answers (1)

HLGEM
HLGEM

Reputation: 96570

It is a good practice to store the key as an integer unless you need leading zeros. You want the key to be the smallest size possible to make joins faster.

Most databases have a way to set an incremental key automatically and if you want one, this tends to be the best way to do it unless you cannot afford to have any numbers missing in the sequnece due to rollbacks. There are really only few types of things that might have a leagl or regulartory requirement that you can't skip items in a sequence, so the autogenerated id is one of the best choices if you want to use a surrogate key. Do not make your own incremental key unless you need to as you will not do this as efficiently as the database will do the automated key and if you get it wrong, you may have race conditions and the child tables could end up assigned to the wrong parent ID.

If you have a guaranteed unique value (that is unchanging), you can use the natural key instead of a surrogate. It might slow down joins some, but it also might mean that you don't have to do as many joins. However, if you use a natural key make sure it is in fact unique and that it will only rarely change. Things like person name,company name, email-address, etc are not good candiates for a antural key, automobile VIN number is. Remember you don't want to change ten million child records because the company name changed.

Upvotes: 2

Related Questions