September 30, 2011 at 11:41 pm
Dear DBA's,
I'm designing a database for a membership issue. I have checked some other popular designs for membership and I saw some people used username(nvarchar(200)) as foreignkey in their design. I thought it is not a good idea to work with userName(nvarchar(200) instead of userID(int) in foreign keys and etc until now, and so I've shocked.
Can someone please explain me what is the benefits of this way?
Is it better to work with username instead of userID and why?
Thanks and regards,
Ashkan
Best Regards,
Ashkan
October 1, 2011 at 2:42 am
Any Ideas?
If you think UserID is better please tell me.
I prefer userID because it takes only 8 byte and also because of data validation. but some people say it make db difficult to use because username is uniq and you should join tables to get user name in the code.
Best Regards,
Ashkan
October 1, 2011 at 11:56 am
That's covered in any T-SQL 101 course, even SQL for Dummies.
The key should be small as possible. Sometimes you "have" to use a char/varchar column as a key, but performance wise (among many other reasons), that's a very bad idea.
Pick up a book on introductory TSQL and make sure you understand it. In the future you should also not be surprised that you're surprised to see other people's designs have flaws. The important thing is to know why and avoid doing the same.
October 1, 2011 at 12:17 pm
ashkan siroos (9/30/2011)
I'm designing a database for a membership issue. I have checked some other popular designs for membership and I saw some people used username(nvarchar(200)) as foreignkey in their design. I thought it is not a good idea to work with userName(nvarchar(200) instead of userID(int) in foreign keys and etc until now, and so I've shocked.
Natural key vs artificial key. There's no right answer here, there are reasons for both sides of the argument.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2011 at 12:42 pm
@gilamonster: could you please describe more?
As I see , This is not only my problem:D
Best Regards,
Ashkan
October 1, 2011 at 1:24 pm
ashkan siroos (10/1/2011)
@GilaMonster: could you please describe more?
I could, or you could use a search engine and do some reading. There's a lot out there, far more than I could reasonably post here.
As I see , This is not only my problem:D
That almost sounds like 'I'm right and anyone doing it the other way is wrong'. Don't fall into that trap.
While an nvarchar(200) is not a good key, it's not because all keys should be meaningless integers (artificial key). NVarchar(200) is a bad data type for username in general (when did you last see a username over 50 characters long?)
Using natural keys in database design is a valid option. Using natural keys for foreign keys is a valid option. There are reasons to use natural keys, there are reasons to use artificial keys. There is not a 'one true answer' here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 2, 2011 at 12:00 am
@gilamonster, thanks for fast reply. As you said, I'm reading some articles about this, and I wanted to know what experts like you prefer.
That almost sounds like 'I'm right and anyone doing it the other way is wrong'
Well, I didn't mean that because I ask the question:D
As you see, mendesm think the answer is simple, like me before this question, so you can find many amateur DB designers who think wrong about the professional design.
I'll be search more and ask my questions about the whole Idea again.
Thanks and Regards
Ashkan
Best Regards,
Ashkan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply