SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 uniqueID vs unique Descriptive name		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mary_itohan
Posting Yak Master

179 Posts
Posted - 11/05/2012 :  01:20:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3442 Posts
Posted - 11/05/2012 :  04:39:51  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/
Go to Top of Page

denis_the_thief
Constraint Violating Yak Guru

Canada
470 Posts
Posted - 11/08/2012 :  12:52:16  Show Profile  Reply with Quote
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.
Go to Top of Page

chadmat
The Chadinator

USA
1961 Posts
Posted - 11/09/2012 :  03:24:18  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000