digital_soul
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
> 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
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
Other than the type, it is created just the same way as with int foreign key constraints.
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
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
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.
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.
ASKER