Relationship Between ASPNET Membership Provider Tables and Custom Membership Tables

Question

Hi.

I went through a custom profile provider example a while ago and I am now revisiting it.

My database has all the dbo.aspnet_* tables created when I ran the aspnet registration wizard. In these tables I have aspnet_Profile which has a FK constraint pointing to aspnet_Users.

I also have two tables in MyDB: The first, dbo.ProfileData, has a foreign key constraint pointing to dbo.Profile.

What I want to understand is how the tables in MyDB relate to those in dbo.aspnet_*. Shouldn't there be a foreign key constraint (or some kind of relationship) between the profile tables in MyDB and the aspnet tables? Some discussion of how my custom tables relate to those provided by aspnet would be wonderful.

Thanks in advance.

Answer 1

There are two options I can see, both of which will yield basically the same result:

• FK from dbo.aspnet_User.UserID to dbo.Profile.UserID, then define a unique key on dbo.Profile.UserID (unless you use it as the PK column for dbo.Profile)

• FK from dbo.aspnet_Profile.ProfileID to dbo.Profile.ProfileID

dbo.aspnet_User is logically 1 - 1 with dbo.aspnet_Profile, so it doesn't really matter which approach you use as you will still get the same relational integrity.

If you are replacing the standard profile data table with your own implementation then it makes more sense to use the first suggestion, otherwise if you are extending the Profile schema then use the second suggestion.

EDIT

aspnet_Profile is the standard table - the standard SqlProfileProvider stores the user's profile data as a serialized property bag in aspnet_Profile, hence why there is no separate aspnet_ProfileData table as well.

This approach allows the profile schema to be customized easily for different applications without requiring any changes to the underlying database, and is the most optimal solution for a framework such as .NET. The drawback is that SQL Server does not have easy access to this data at all, so it is much more difficult to index, update and query the user's profile data using T-SQL and set-based logic.

The most common approach I have seen to remove this limitation is to extend the standard SqlProfileProvider to write to a custom profile data table which has specific columns for application-specific profile properties. This table naturally has a 1-1 relationship with the aspnet_Profile table, so it has a foreign key as indicated above.

The role of the extended provider is to promote specific profile properties to columns during profile writes, and read in the columns when the profile is retrieved.

This allows you to mix-and-match storage solutions on an as-needs basis, as long as your extended provider knows how to fall back to the standard implementation where it does not 'know' about a given property.

I always think it is best to leave the standard membership tables as-is, and extend where necessary using new tables with appropriate foreign keys, then subclass the appropriate provider and override the provider methods with your own implementation (calling into the base implementation wherever possible).