April 8, 2016 at 3:43 am
Hi guys,
Just wanted to ask for your opinion on this matter:
We have an application that uses NT account for the login and we have a user table that contains all the NT Account that have access to the app. The user table has UserID(int, identity increment, Pkey), NTAccount (varchar20), LastName, FirstName columns.
Now, the app is saving transaction data to trans table but is using the NTAccount when saving and not the UserID. So when it comes to the reports, the NTAccount field is join to the NTAccount in user table. The trans table contains hundred thousands of data.
In terms of optimization, isn't it better to use UserID than NTAccount?
Thank you!
April 11, 2016 at 5:17 am
UserID is the surrogate key, so this should be used to identify the row in this table when referenced from other tables.
It is int, so smaller than storing strings in related tables.
It is unique so sql will not iterate to check next row once it found something equal.
The table is clustered on UserID so there will be no lookup penalty when doing joins on this column.
This is actually a design question.
Your DB will grow more quickly if you are natural keys into many tables compared to a surrogate key.
I don't think there is any positive argument anyone can make for not using the surrogate key in this situation.
April 11, 2016 at 7:10 am
Forget about optimization for a second. Think about normalization. What are you going to do when a user name changes? Somebody changes their name and suddenly you have to update every row in every table for that user. That is just plain awful.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 11, 2016 at 7:19 am
Thank you, guys! It made sense to me. I will raise this up to the app developer. =)
April 11, 2016 at 9:10 am
April 11, 2016 at 1:22 pm
Sean Lange (4/11/2016)
Forget about optimization for a second. Think about normalization. What are you going to do when a user name changes? Somebody changes their name and suddenly you have to update every row in every table for that user. That is just plain awful.
Whle I agree with the suggestion, I disagree with the wording. If UserID and NTAccount are both candidate keys in the table, then either of them can be used as the foreign key without violation any normalisation rules.
Introducing surrogate keys (which UserID in this case might or might not be, depending on context), choosing which of the candidate keys will be primaray key, and choosing which candidate key will be used to impement a foreign key, are all optimization choices. They should be made after the logical database design, during implementation. And the choice will often be different depending on the specific RDBMS used to implement the data model.
April 11, 2016 at 1:58 pm
Hugo Kornelis (4/11/2016)
Sean Lange (4/11/2016)
Forget about optimization for a second. Think about normalization. What are you going to do when a user name changes? Somebody changes their name and suddenly you have to update every row in every table for that user. That is just plain awful.Whle I agree with the suggestion, I disagree with the wording. If UserID and NTAccount are both candidate keys in the table, then either of them can be used as the foreign key without violation any normalisation rules.
Introducing surrogate keys (which UserID in this case might or might not be, depending on context), choosing which of the candidate keys will be primaray key, and choosing which candidate key will be used to impement a foreign key, are all optimization choices. They should be made after the logical database design, during implementation. And the choice will often be different depending on the specific RDBMS used to implement the data model.
I would think in this case that NTAccount is a candidate key but a poor choice for a primary key since the value can be changed. If my NTAccount is "slange" but I change my name to "ssmith" then we have a kind of nasty situation to deal with if the primary key chosen was NTAccount.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 11, 2016 at 3:51 pm
Both UserID and NTAccount should have a unique index. If UserID is clustered, then that should be the fastest access path.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 14, 2016 at 4:09 am
Both UserId and NT Account are unique index and UserID is clustered. And yes, NT Account can be changed anytime for a particular user specially for females who just got married for example.
thank you for pointing this one out!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply