|
|
Posted by Tony Rogerson on 12/29/05 11:46
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. When passing back results, all the joining is done on the 'id' and
you only pass back the 'name' for the small subset of data you are
presenting to the user.
An example schema is as follows :-
create table Logon (
id int not null identity constraint sk_logon unique clustered,
name varchar(15) not null constraint pk_logon primary key
nonclustered,
password varchar(15) not null
)
In other tables you would use Logon.id and not Logon.name, so if you had a
permissions table say you'd do it like this...
create table Permission (
id int not null identity constraint sk_permission unique
nonclustered,
logon_id int not null references Logon( id ),
security_ticket_id int not null references SecurityTicket ( id ),
constraint pk_Permission primary key clustered ( logon_id,
security_id )
)
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.
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns973AE2E0716BBYazorman@127.0.0.1...
> 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]
|