I use asp membership in application.
I added UserProfile table and it has foreign key to Users(of asp membership).
As a foreign key I use Username because username is email and it's unique.
And anywhere where I need to reference user I use Username as foreign key.
From application when I need to get profile for example I pass Username to stored procedure to get data.
I just wonder if this is the good way to do this. Is there some potentional security issue here?
Asked
Active
Viewed 1,052 times
0
1110
- 7,829
- 55
- 176
- 334
3 Answers
6
The main issue that I see here is that you spend a lot of "data space" for a foreign key and this will make it slow and eat database space for your tables. Also you database table will connect making string compare - database take care and make hash for this strings and behind is make a number compare, but have a little small overhead on that.
Just make the UserName unique and use a number foreign key to connect it with the rest table.
The second issue here is when a user need to change their email, or give it wrong for any reason. In this case you need to update all the connections on the database and make sure that there is not other similar email.
And one more issue is that the email and the foreign key can be case sensitive or not. If for any reason you make it case sensitive then you make a mess.
About security issues, you always need to open and ask your database using parameters. This is the same for a number key or for a string key, so this make no different at this case.
Aristos
- 66,005
- 16
- 114
- 150
-
Yes that space can be the problem. Is it then better to use aspnet_Users UserId it's type is uniqueidentifier? – 1110 Apr 11 '12 at 11:27
-
@1110 Yes is uniqueidentifier - this is better for many aspects. – Aristos Apr 11 '12 at 12:15
2
I would say no for one simple reason: many systems allow users to change their usernames. In your case, you link this to an email address, which users should be allowed to change.
If you use it as a foreign key, you have to run updates to keep your data in sync, and that is bad.
IamIC
- 17,747
- 20
- 91
- 154
0
This is an old natural vs. surrogate key discussion. There are "fans" of either approach, but the simple truth is that both have pros and cons, and you'll have to make your own decision that best fits your particular situation.
For the specific case of e-mail as PK, you might want to take a look at this discussion.
Community
- 1
- 1
Branko Dimitrijevic
- 50,809
- 10
- 93
- 167