Best database design approach to join complex data to ASP.Net Membership tables
I’m looking to use a slightly modified ASP.Net Membership Provider to handle the standard user account creation/authentication/etc in a website. We have a fair amount of legacy data that I need to migrate in order to provide continuity with our existing users’ saved information (like order history, wishlist, etc). [NOTE: We have to migrate anyway, so this is not the reason we’re migrating data]
I’m wondering what a sensible approach is for joining this additional data to the asp.net membership tables. There are a two unique keys that I could use on the user table – UserId or email – which we will use as a surrogate for username.
My question is, what is the better access pattern (and hence foreign key) to use elsewhere in my tables for storing orders, wishlists, etc?
In HttpContext, a User object is available that contains the “Username”, our email address,
but doesn’t have the Guid for userId available.
I see 3 options:
- I’d like to simply use the uniqueidentifier UserId for efficiency of access over a lengthy varchar email address, but it doesn’t appear readily available without extra database calls to fetch it via email/login. Is there some way to get the UserId so I can make it the foreign key on different tables?
- I can pass in email address (username) as the input parameter, join on userId where email address = @emailAddress, and use userId as the foreign key on other tables.
- I can store username/email address on all the other tables as a foreign key – which would be an unfortunate denormalization
Any thoughts on a best method from either a DB perspective or from an application efficiency perspective?
One Solution collect form web for “Best database design approach to join complex data to ASP.Net Membership tables”
You can get UserId:
MembershipUser myObject = Membership.GetUser(); string UserID = myObject.ProviderUserKey.ToString();
or maybe (please, check it)
string userId = Membership.GetUser(User.Identity.Name).ProviderUserKey.ToString();