|
|
Posted by Erland Sommarskog on 12/30/05 00:39
Tony Rogerson (tonyrogerson@torver.net) writes:
> The ID thing is a misconception by many DBA's, what really happens in an
> application is this...
>
> Consider a list of names in say a drop down list, you would code the value
> part as the 'id' and the text part as the 'name'.
>
> When a user selects an entry from the drop down you pass the 'id' back to
> the database and not the 'name', you then use the 'id' on the query etc...
> Basically, the 'name' is used only as meta data for display; of course, if
> its a textbox then the user enters the 'name' and thats used, but
> applications tend not to work like that - most choices are drop downs,
> checkboxes, radio buttons; users don't always remember the full text of
> 'name'.
>
> Now bear the above in mind and re-read your reasoning, suddenly you have
> very narrow tables and you get better performance because joins are on 4
> bytes rather than 20 / 30 etc... storage is reduced because of the same
> reason.
There are of course lots of situations where this strategy is the way
to go. For instance, say that users want to be able to define customer
groups and add customers to them, for statistical purposes or whatever.
Since it is likely that the user would like to have long descriptive
names for their groups, the names are not really good for a key. Not
the least since the users may want to change the group names everyonce
in a while.
A username in a login table is a little different. Usernames are
typically fairly short. They are also less prone to changes. In fact,
you could consider it a business rules that they should not change.
There is always a trade-off in these situations. An id may take up
less space - but you will have to join to the Logins table each time.
And performance is not everything. One advantage with using the login
name as key, is that when you review auditing data or columns, you
see the username directly without joining. The same argument applies
to a customer group as well, but I far more have reason to look at
user-id columns from Query Analyzer than customer-groups ids.
> I think, if I have time I'll write an article over this surrogate key
> stuff and how it should be used in the application - it seems to be one
> of the biggest misunderstood methods in the db space at the moment.
I think most knowledgeable SQL users knows this concept well. The
difficult part is to know when to use it, and when to not. Usernames
is a case where I think a surrogate is a bad idea.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|