Managing users when using a stand alone database as the membership provider

Question

When using a stand alone database as a membership provider, is it usual to then have an application specific users table in the database serving the application? For example say I have an application that manages messages for a user. Normally I would have a user_messages table and reference the users table foreign key? However if I just use the id in the membership db I can not reference the messages by a user by foreign key.

Any pointers much appreciated

Answer 1

"if I just use the id in the membership db I can not reference the messages by a user by foreign key"

The issue here is enforcement of the foreign key. I don't know of any DBMS which supports foreign keys which reference a table across a database link. And even if it were possible would it be desirable? Such an arrangement would mean that we couldn't insert child records in our local database if the remote database is offline for any reason.

The normal workaround in such a scenario is replication, that is, maintaining a copy of the remote table in the local database. If we have a DBMS which supports remote Materialized Views this is can be a pretty straightforward implementation.

Answer 2

I'm interested in this too. This probably doesn't qualify as an answer - but I'll update once I've implemented.

I'm also using the ASP.NET Membership Provider with my web-app; although I've included the SQL tables and Procs for the Membership Provider in my database I have maintained logical seperation so you could run it seperately if you wanted too.

Although you've centered on the database issues here (which is fair enough) it's important to remember that with the ASP.NET Membership Provider you're dealing with more than just a database. The thing about the ASP.NET Membership Provider is the provider itself - the actual data repository is abstracted out.

I'm assuming that with the ASP.NET Membership Provider I'll be able to use the services/API of the provider itself to help manage the relation ships you're talking about. So I won't be relying on the database specifically - because the "provider" is the external facing interface I should be going through, and using it would tie me to that data source.