uniqueID vs unique Descriptive name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

uniqueID vs unique Descriptive name

Author

Topic

mary_itohan
Posting Yak Master

191 Posts

Posted - 2012-11-05 : 01:20:58

Helloo guys,
quick question.

I have a table with a primary key and a name, called categories.

Both the name and PK are unique keys and have unique constraints on them.

It makes reference to another sub_categories table with a FK. However am wondering if its better to use the PK which is an int value or a more descriptive name which also has a unique constraint on it to reference the foreign key ?

As when I look at the table without using a view/query its easier with the descriptive name

pls advice
many thanks
M

_____________________

Yes O !

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-11-05 : 04:39:51

this is a purist vs practicality problem:

The purist would say that you have a natural key already ( the category name ) - therefore you should use that as your key and form the foreign keys on that.

Practically though - this means that every foreign key uses up more space than the simple numeric key would use (an int is 4 bytes) -- a varchar(50) is 52 bytes.

Also practically if your key is also your Clustered index then you'd be inserting into an ordered structure with un-ordered data which will cause shuffling. Better to use a surrogate auto number key.

I'd go with the surrogate key for must real world situations.
I'd

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2012-11-08 : 12:52:16

In our system we use

Descriptive Names
- small tables (under 1000 records)
- Look up Tables, codes, statuses
- Tables maintained by DBA or System

The space usage and shuffling issue is of little relevance in these cases. It means the PK has meaning just by looking at it. Also in these cases we are not looking to change the PK value.

int value/surrogate auto number key
- large tables (over 1000 records)
- Tables that are constantly added to it, like Orders
- Tables were users add the information

In this case the space usage and shuffling issue Charlie mentions is very Relevant. Since these tables are constantly being inserted, users are not required to (nor could they practically) come up with distinct values.

I suppose you could say our approach is not consistent, but it has worked well for us.

chadmat
The Chadinator

1974 Posts

Posted - 2012-11-09 : 03:24:18

In addition, if your key is your clustered index key, the size of your NC indexes will be bloated if you use the varchar(50) (For example) as opposed to the int.

-Chad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources