Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Creating foreign key relations to ASP.NET Users uniqueidentifer field

Posted on 2010-09-10
7
Medium Priority
?
714 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 1000 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 1000 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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:Adam
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Integration Management Part 2
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

581 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