Posted by Erland Sommarskog on 12/28/05 23:18

Cecil (cecilkain0@yahoo.com) writes:
> So if I later had an employee say, that needs to login, rather than add
> a username,password to the Employee table I could simply add a LogonID
> field to the employee table to link it w/ their identification record
> in the Logon table.
>
> Do you think this is a bad idea?

The ID is superfluous when you have a natural key in the username.
Sometimes surrogates keys are called for.

> Also I thought it would be faster to always use an int ID as my primary
> key instead of a string for searching and joining.

Or it's slower. Say you want to display list which includes the username.
If the username is the foreign key, it's already in the table. With an
ID, you will have to join to the Logins table. And the ID column makes
the table larger, and more space means worse performacne.

The true story, that this is the wrong place to look for performance in,
Whatever you do, it is not likely to have any measurable effect, as I
suspect the volumes will be modest here. Manageability is much more
important, and a username without ID appears more manageable here. The one
case where an ID is nicer, is when a user wants to change his username.

> If I were to have a foreign key linking to the logon table I'd have to
> stick the whole string as the foreign key instead of just an int. So it
> was my plan to make sure each table had an int primary key even if it
> was possible to uniquely id a record by an already present column like
> username.

That's a bad plan. Surrogates are sometimes called for. For instance,
an Orders table typically as an integer key generated by the system.
But an OrderDetails table should have a two-column key with OrderID
and RowNumber (or ProductId).




--
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

[Back to original message]