Index for username/password

[Cecil]

Does this make sense for a logon table:

CREATE TABLE Logon
(
ID INT NOT NULL IDENTITY PRIMARY KEY,
name VARCHAR(15) NOT NULL,
password VARCHAR(15) NOT NULL
)
GO
CREATE UNIQUE INDEX IX_Logon_Name ON Logon(name)
CREATE INDEX IX_Logon_NameAndPassword ON Logon(name,password)
GO

I do want the name to be unique but also will search frequently on both
name & password. Is this how it should be done? I don't fully
understand the difference between placing a single index in name &
password VS one on both name & password.

[Erland Sommarskog]

Cecil (ce********@yahoo.com) writes:

Does this make sense for a logon table:

CREATE TABLE Logon
(
ID INT NOT NULL IDENTITY PRIMARY KEY,
name VARCHAR(15) NOT NULL,
password VARCHAR(15) NOT NULL
)
GO
CREATE UNIQUE INDEX IX_Logon_Name ON Logon(name)
CREATE INDEX IX_Logon_NameAndPassword ON Logon(name,password)
GO

I do want the name to be unique but also will search frequently on both
name & password. Is this how it should be done? I don't fully
understand the difference between placing a single index in name &
password VS one on both name & password.

I don't see the purpose of the ID column? Why not make the name the primary
key?

The index on (name, password) does not seem very useful here. Usually an
index on the form (uniquecolumn, othercolumn) is not meaningful, but it
can be sometimes, to achieved so-called covered queries. But as long as
the table does not have lots of other columns, it's difficult to see a
case for it here.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Cecil]

>>I don't see the purpose of the ID column? Why not make the name the primary

key?

I was thinking of doing that, but I intend for the Logon table to be
like an ID card. Only for efficient identification. I wanted to reuse
this table design in multiple projects that would require
authentication.

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?

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.

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.

Again, do you think this is a bad idea? What would you name the foreign
key to a varchar username field? usernameID? It just seems like it
should be a number to me if it has ID appended to it. I like using ID
becuase I know it is a key of somekind when I see it but maybe I
shouldn't do that.

I was reading a post by someone earlier who suggested to me that all
field names be unique across my schema. So if I understand him
correctly:

LogonID, LogonName, & LogonPassword would be better field names.
LogonPassword seems sorta like overkill compared to just password but
if you're going to be unique you might have another field called
password in another table so I guess you'd have to do it that way.
Almost like table-qualifying each field name.

I'm starting a simple DB from scratch so I'm trying to use as good a
practices as I can and would be very interested in your reccomendations
Erland. Thanks.

[David Portas]

Cecil wrote:

I don't see the purpose of the ID column? Why not make the name the primary
key?

I was thinking of doing that, but I intend for the Logon table to be
like an ID card. Only for efficient identification. I wanted to reuse
this table design in multiple projects that would require
authentication.

Name would still be unique though wouldn't it? So it should still have
a unique constraint on name.

Storing passwords in the database is an inherent security flaw. Don't
store them, encrypted or otherwise. If you must, store a secure hash of
the password. If you are using SQL Server 2005 then use the built in
encryption / authentication. Where possible, use integrated security
rather than invent your own.

--
David Portas
SQL Server MVP
--

[David Portas]

David Portas wrote:

Cecil wrote:

>I don't see the purpose of the ID column? Why not make the name the primary
>key?

I was thinking of doing that, but I intend for the Logon table to be
like an ID card. Only for efficient identification. I wanted to reuse
this table design in multiple projects that would require
authentication.

Name would still be unique though wouldn't it? So it should still have
a unique constraint on name.

Apologies, I see that you have declared a unique INDEX on name. A
unique CONSTRAINT is virtually equivalent however and is usually the
preferred choice rather than an index.

--
David Portas
SQL Server MVP
--

[Cecil]

I agree Windows Auth is the way to go, but this DB is for a website and
as such, Windows Auth is not practical.
I was planning to encrypt the password using .NET before storing it in
the DB.

I'm not sure what the built in encryption / authentication SQL2005 has
other than Windows Auth. Is there another feature?

I used an unique index on name because I wished to have fast lookups of
names. I thought an index was how to best accomplish this, No?

I'm not possitive when to use indexex on a column and when to do so on
multiple columns. I don't get the difference.

[Tony Rogerson]

I'd still have the 'ID' column but make it a surrogate key instead and use
that on other tables, may be a permissions, for example....

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

Then in the application use 'id' everywhere, it encapsulates the data and
allows for 'name' to change without breaking the application logic.

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Cecil (ce********@yahoo.com) writes:

Does this make sense for a logon table:

CREATE TABLE Logon
(
ID INT NOT NULL IDENTITY PRIMARY KEY,
name VARCHAR(15) NOT NULL,
password VARCHAR(15) NOT NULL
)
GO
CREATE UNIQUE INDEX IX_Logon_Name ON Logon(name)
CREATE INDEX IX_Logon_NameAndPassword ON Logon(name,password)
GO

I do want the name to be unique but also will search frequently on both
name & password. Is this how it should be done? I don't fully
understand the difference between placing a single index in name &
password VS one on both name & password.

I don't see the purpose of the ID column? Why not make the name the
primary
key?

The index on (name, password) does not seem very useful here. Usually an
index on the form (uniquecolumn, othercolumn) is not meaningful, but it
can be sometimes, to achieved so-called covered queries. But as long as
the table does not have lots of other columns, it's difficult to see a
case for it here.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Cecil]

Yeah I think that's a good idea Tony.
That's essentially what I had in mind, perhaps making the ID a
surrogate key does better model what I'm doing w/ it.

[Erland Sommarskog]

Cecil (ce********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Cecil (ce********@yahoo.com) writes:

I agree Windows Auth is the way to go, but this DB is for a website and
as such, Windows Auth is not practical.
I was planning to encrypt the password using .NET before storing it in
the DB.

I'm not sure what the built in encryption / authentication SQL2005 has
other than Windows Auth. Is there another feature?

SQL 2005 has a whole slew of encryption stuff with asymmetric keys,
symmetric keys, certificates and God knows what. And they are not
dependent on how you log in.

Encryption is not my best subject, but you are probably right encrypting
the password already in the app. Sending it in clear text over the wire
is not that good.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Tony Rogerson]

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" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Cecil (ce********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Tony Rogerson (to**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Cecil]

I suspect you're both right. I'm much more of a programmer than a DBA
which is why I like to get opinions on table deign from more
knowledgable people than myself. But I think Tony is right in implying
as a programmer your table design decisions may seem odd when in fact
the "extra key" in a table to a web-app may be a small price to pay for
an efficient and reliable key in a long list of items on a web page, or
even a rich client for that matter.