Associating data with a user

For my first ASP.NET MVC 3 application, I'm using the aspnet_Users and aspnet_Roles tables to provide authentication for my users and for a few roles. That seems to work pretty well. Additionally, I want to make associations between the logged-in user and rows in various tables.

As a concrete example, I've got a Recipes table which has columns specific to recipes (name, dates, attributes) as well as a UserID column. That UserID column is currently a foreign key to a Users table in my IceCreamDB (NOT the aspnet_Users table in the aspnetdb) which contains various domain-specific information about users of the system. So, it's easy enough to create a query that retrieves all of Matt's recipes, by creating a user in my Users table named Matt who has some integer UserId and then use that UserId during Creates and Updates to the Recipes table. Great.

To tie the logged-in user "itsmatt" (from the aspnet_Users table) to my IceCreamDB's Users table UserId for Matt, I have a Guid column in IceCreamDB Users table which is filled in with the aspnet_Users Id (its a Guid) for the login.

IceCreamDB's Users table:

 UserId     1               // primary key used as FK for other tables 
 UserName   Matt
 Phone      555-1212
 Department Product Development
 Building   2-A
 Office     221
 UserGuid   7fc75a6c-7e32-43f3-be8c-be0122bf54cb // Guid from aspnetdb User table

And this works OK - as part of the user registration process, I create an aspnet_User, set up whatever roles (e.g., "Administrators", "Owners", "Production") are appropriate, and then create a user entry in the IceCreamDB Users table, copying the Guid into the new row. This lets me log into the website and see my recipes or my orders.

But I feel I've home-brewed this solution and there's likely a better, different approach to doing this. I'd like some guidance on this.

回答1:

As far as I known, this is a quite common solution. The 2 other I'm aware of are using aspnet_Profile for simple settings, or editing the aspnet_Users table, which is not a good solution, because if you'd have more applications (aspnet_Applications) using the same aspnet_Users your end up having fields on that table which one application might use (not nullable) and the other doesn't.

In this tutorial they are basically doing the same thing.

回答2:

That's OK to me. The Membership provider also works the other way around - it has a provideruserkey property that's stored in the DB that would contain the UserId value of 1 in the aspnet_users table.

So if its good for Microsoft, it aught to be good for your scenario :-)

HTH.