CODEWITHSUNDEEP
asp.netsql-serverdatabase-designasp.net-membership
jkelley
jkelley

Reputation: 2640

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:

  1. 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?
  2. 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.

Not viable

  1. 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?

Upvotes: 1

Views: 853

Answers (1)

garik
garik

Reputation: 5756

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();

Upvotes: 3

Related Questions