2

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.

Matthew
  • 10,244
  • 5
  • 49
  • 104

2 Answers2

4

This question is actually about two different things.

  1. Should a foreign key to a remote database also a be a foreign key to a local table.
  2. Can you maintain referential integrity to a foreign database

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:

  • a comments table that stores the facebook_id as foreign key

or

  • a separate 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.

Hazzit
  • 6,782
  • 1
  • 27
  • 46
  • 1
    In my case I *do* have a good way to know when users are added or removed from the remote database. In fact, it is simply a separate DB on the same server instance... but this isn't really important to the philosophical point of the question. The two approaches you've setup are basically **all** of your tables trust that a user is valid or **one** table trusts that a user is valid. – Matthew Oct 18 '13 at 23:07
  • 1
    @Matthew Correct. It basically depends on how tightly the two databases are coupled. Using a single column to link to a remote database is a more robust approach since only that column depends on the remote DB, but it does introduce an extra level of complexity (your own ids). In the end you'll have to evaluate if this robustness is worth the extra work. – Hazzit Oct 18 '13 at 23:18
2

Establishing referential integrity to an external database is not a good idea. You should keep your own id's then have External ID's and then instead of a foreign key constraint, use an index if you need faster lookups.

Reason being that even if you manage somehow to establish this relationship, it becomes a coupling which makes you vulnerable to to changes in the other system.

IMHO: You should maintain what you're requesting in a business layer integration, not from at database level.

implement a change notification system, ideally with bi-directional service in the auth system which your system can subscribe to which will be invoked as the BI of the auth system changes.. But if not, another approach again if you have a business layer, is to farm the provider for changes, but that is difficult because there always will be a delay or the performance impact would be significant.

I believe your best approach is to replicate the answer in terms of roles etc returned in your system with usage of auth system change date stampels, then use the changed date of the role provider api system to know if you can use your own or have to get it again, that system is pretty easy to implement, but should happen in another layer than DB.

In DB should just be an ExternalID column for instance which possibly would be coupled with a VerificationDate, both updated when used successfullY (or cleared if not) just to keep it simple, but do not use cross database relational integrity, like with fully prefixed instances as it is a killer for your performance and a weakness beyond control (your BI doesn't control why other BI want to update)

T. Nielsen
  • 835
  • 5
  • 18