Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mogwai
Starting Member
1 Post |
 Posted - 2009-11-23 : 22:54:16
|
| I have the following tables:[User]*UserId | intUserNamePasswordFirstNameLastNameetc...- - - - - - - - - - - - [UserEmail]UserId | intEmailAddress | varchar(100)IsPrimary | bitIsVerified | bit- - - - - - - - - - - - My question is should I have a surrogate key on [UserEmail] (i.e EmailId)? Also, should I make UserId/EmailAddress the primary key as that makes logical sense. And Lastly - if my most common query will always be:- - - - - - -SELECT [User].UserId,[User].FirstName,[UserEmail].EmailAdress,etc...FROM [User]JOIN [UserEmail] ON [User].UserId = [UserEmail].UserId AND [UserEmail].IsPrimary = 1WHERE [User].UserId = (SELECT UserId FROM UserEmail WHERE LoweredEmail = @email)- - - - - - -What would you recommend for indexes on the [UserEmail] table?Thank you much for your help.-Jim |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-11-23 : 23:39:22
|
| What happens when someone gets married? Are you going to disallow them to change their userid? My company allowed me to change my userid and email address when I got married. For this reason a surrogate key should be used. But if you can guarantee that a userid will never change, then a surrogate key is not needed.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
|
|
|
Do I need a surrogate key?