If I have a separate membership provider API, which doesn't store credentials and roles in my database, how should I maintain referential integrity with my application's reference to users?
For example, we interface with the membership API but passing it a member name and basically requesting the access profile or a role, but I do not have access to the underlying database.
We can then use the returned role or profile to control access within the application. The problem with this approach is that when we persist information about that user's actions (such as logging their changes, or assignment of tasks in workflow) we store the User's ID from our provider, but since the information isn't in our app DB we can't FK to it to have DB integrity.
And, really, this makes sense, because the membership provider has no contract with us to ensure that their changes don't violate FK's in our application DB.
But it seems like I should be able to aggregate information within my own app DB by user, or have something to enforce a reference on my persisted UserIDs.
I am considering a separate "thin" users table with some qualatative information like name and the ID from our provider... this table would get populated probably on first login.
The benefit is that I can now aggregate user information against some user information solely in my application and I can enforce the references within my app DB. A downside is that I am duplicating user data, which is potentially stale.
This question is actually about two different things.
These are two different things entirely, although the quick answer to both is actually the same:
No.
But let me get into the details a bit.
1. Use of foreign keys to a remote database
To reduce dependancy on the remote database, you should only store those foreign keys in one location in your database.
Example: Let's just say you had a blog where users could post comments. These users will login through Facebook. You now have a remote database (Facebook) and a local one that stores your users' comments. You could now follow one of two designs:
comments table that stores the facebook_id as foreign key or
users table storing the facebook_id along with a local id and a comments table that uses your local id as foreign key.You should not use the facebook_id in both. While that would actually work, you're introducing a dependancy on a remote database without need. You wouldn't be able to add a comment from a Non-Facebook user since that would break your design.
2.Referential integrity with remote databases
You might not have intended to ask this, but the term referential integrity implies all foreign keys to the remote database actually refer to an existing remote record (i.e. user). The only way to maintain that integrity would be if the remote database would inform you of changes to a remote record or its deletion, which usually is not the case.
Example: Lets go back to above mentioned hypothetical blog. Some Facebook user posted a comment. Later the same person decides to delete their Facebook account. The Facebook database will not likely inform you of that happening, leaving you with "dead" records in your database which do not link to a valid record in the remote database anymore. This breaks referential integrity. So unless you have a good way of actually maintaining that integrity, such as receiving deletion notifications etc, you should design your application so that it won't break if the Facebook user got deleted.