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.

  • Is FieldByName injection-safe?
  • DBMS Performance-Tuning Book Recommendations
  • Using Entity FrameWork with multiple MS SQLSERVER Databases
  • SSRS Remove Column from Report
  • How to batch update from mssql compact edition to mssql database server?
  • Best way to create a temp table with same columns and type as a permanent table
  • 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?

  • customer expense query
  • Using UNION ALL in STUFF / XML Path
  • Why is “0D0” considered numeric in SQL Server 2008?
  • Select all columns from all tables in SQL Server 2008
  • SQL Table partitioning
  • How to insert a c# datetime var into SQL Server
  • 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();
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.