Re: Index for username/password

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" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns973A9A6DF18F1Yazorman@xxxxxxxxxxxx
> Cecil (cecilkain0@xxxxxxxxx) 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, esquel@xxxxxxxxxxxxx
>
> 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


.

Relevant Pages

  • Re: Syntax of derby db & squirrel-sql
    ... CREATE TABLE customers ( ... id int generated always as identity constraint cust_pk primary key, ...
    (comp.lang.java.programmer)
  • Re: Storing Users/Groups
    ... websites can interface with this setup. ... > returns @outtable table (root_node int, group_key int, primary key ... > --40 members are members of 50 ... PRIMARY KEY, ...
    (microsoft.public.sqlserver.programming)
  • Re: Storing Users/Groups
    ... returns @outtable table (root_node int, group_key int, primary key ... --40 members are members of 50 ... PRIMARY KEY, FOREIGN KEY REFERENCES, ... Is it still possible to query all users of the websites with this additional functionality? ...
    (microsoft.public.sqlserver.programming)
  • Re: Views vs Stored Procedures, whats the difference?
    ... I'm just trying to differentiate between two fundamentally different SQL objects. ... CREATE TABLE P(pk INT NOT NULL PRIMARY KEY, ... SELECT MAXFROM nestedview ...
    (comp.databases.ms-sqlserver)
  • Re: Adding new object to Typed Data Set and return the primary key
    ... CAST(TS AS INT) AS TS FROM AgencyNET.Office " + ... public TypedDataObjectProject newProject() { ... returning the new Primary Key value. ... on the singleton which then creates a new table adapter. ...
    (microsoft.public.dotnet.framework.adonet)