|
|
Posted by Tony Rogerson on 12/28/05 17:42
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@sommarskog.se> wrote in message
news:Xns973A9A6DF18F1Yazorman@127.0.0.1...
> Cecil (cecilkain0@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, 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]
|