Creating foreign key relations to ASP.NET Users uniqueidentifer field

Hi Experts

Im sure there is a simple answer to this, but its not something Ive come across before and im a little bit unsure as to how to proceed.

I have a small number of users as part of an admin site that are created and managed by ASP.NET's membership provdier. As such they have been created in SQL Server with the UniqueIdentifier data fields as their identifying ID.

I have several other tables that I need to create a foriegn key relationship to the user the record relates to. usually id just be dealing with Integer values, but what datatype should the foreign key field be? Do I really need to store the uniqueidentifer value in every foreign key record?

Many thanks

Stewart
LVL 2
digital_soulAsked:
Who is Participating?
 
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
Basically yes. The UniqueIdentifer is the key field for the aspnet_Users table so is the field you should use to reference foreign tables.
0
 
icemokkaConnect With a Mentor Commented:
You could add a int field to the user table with an identity seed (which is managed by SQL)
Then you can link that int field to your other tables.

Or you can write your own user management system with an int field
0
 
digital_soulAuthor Commented:
Thanks. Is there a strong arguement to take one approach over the other? If I simply reference the uniqueidentifier field in all fireign tables, what datatype should the foreign userId field be?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
cyberkiwiCommented:
> Thanks. Is there a strong arguement to take one approach over the other?
For simplicity and portability (to any asp.net app), link by the uniqueidentifier is a good approach.

> If I simply reference the uniqueidentifier field in all fireign tables, what datatype should the foreign userId field be?
It should be the same, i.e. uniqueidentifier
0
 
cyberkiwiCommented:
Other than the type, it is created just the same way as with int foreign key constraints.
0
 
icemokkaCommented:
It's kinda a question of personal preference.

There are 2 extra advantages in my approach to my opinion
- Less expensive to store & index an int <-> guid
- An int is much easier to read when you need to stroll though your tables for debugging/troubleshooting

 
0
 
AdamSenior DeveloperCommented:
If the table uses a GUID as the primary key, then this is what you need to use to identify the record in other tables, UNLESS there is another column that UNIQUELY identifies the row, in which case that can be used.

I presume the schema you are using is the one defined here: http://msdn.microsoft.com/en-us/library/aa478949.aspx#data_schema

If so, no other columns stand out as alternative keys. Although the email address would be unique, it too is a long field, and can also change, so you'd need to ensure changes are cascaded to all child tables otherwise the relationship would get broken.

So, what can you do?
Leave as it is, and carry on using the GUID as the key. It will take more space, and you will need to ensure any indexes that use the UserId column (probably most of them!) are regularly defragmented, as UID's cause index fragmentation, particularly in clustered indexes. Although GUID's aren't ideal keys, they are useful in distributed databases where there may be a conflict between integers as identity columns, which is why MS would have used them here. They are not ideal, but do work.

You could try changing the UserId column to an int column, populate it with numbers, then test all the Membership provider methods/properties to see if they work (I suspect they won't) but if they do, you've solved your problem.

Create your own custom Membership Provider (see http://msdn.microsoft.com/en-us/library/f1kyba5e.aspx) which will allow you to define your own columns.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.