最佳数据库设计方法将复杂数据连接到ASP.Net成员资格表 [英] Best database design approach to join complex data to ASP.Net Membership tables

问题描述

我正在使用一个稍微修改的ASP.Net会员提供商来处理网站中的标准用户帐户创建/身份验证/ etc。我们需要迁移大量的遗留数据，以便为现有用户保存的信息（如订单历史记录，心愿单等）提供连续性。 [注意：我们必须迁移，所以这不是我们迁移数据的原因]

我想知道加入这个附加数据是什么明智的做法到asp.net会员表。我可以在用户表中使用两个唯一的密钥 - UserId或电子邮件 - 我们将用作代理用户名。

我的问题是，什么是在表中的其他地方使用更好的访问模式（以及外键）来存储订单，wishlist等等？

在HttpContext中，可以使用一个User对象，其中包含用户名，我们的电子邮件地址
，但没有可用的userId指南。 / p>

我看到3个选项：

1. 我想简单地使用uniqueidentifier UserId用于通过冗长的varchar电子邮件地址访问的效率，但是如果没有额外的数据库调用通过电子邮件/登录来获取它，它不会显示出来。有没有办法得到UserId，所以我可以把它作为外键在不同的表？


2. 我可以传入电子邮件地址（用户名）作为输入参数，加入userId的电子邮件address = @emailAddress，并在其他表格中使用userId作为外键。

不可行

3. 我可以将所有其他表上的用户名/电子邮件地址作为外键存储 - 这将是不幸的非正规化

从数据库视角或应用程序效率的角度来看，对于最佳方法的任何想法？

解决方案

您可以获取UserId：

  MembershipUser myObject = Membership.GetUser（）; 
 string UserID = myObject.ProviderUserKey.ToString（）; 
  

或者（请检查）

  string userId = Membership.GetUser（User.Identity.Name）.ProviderUserKey.ToString（）; 
  

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

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

解决方案

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

这篇关于最佳数据库设计方法将复杂数据连接到ASP.Net成员资格表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！