Solved

Creating foreign key relations to ASP.NET Users uniqueidentifer field

Posted on 2010-09-10
7
653 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:digital_soul
7 Comments
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 250 total points
ID: 33644710
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
 
LVL 2

Assisted Solution

by:icemokka
icemokka earned 250 total points
ID: 33644720
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
 
LVL 2

Author Comment

by:digital_soul
ID: 33644735
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33644751
> 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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33644756
Other than the type, it is created just the same way as with int foreign key constraints.
0
 
LVL 2

Expert Comment

by:icemokka
ID: 33644800
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
 
LVL 12

Expert Comment

by:Cyber-spy
ID: 33644831
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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now