I'm designing an application around the provider pattern
(http://msdn2.microsoft.com/en-us/library/aa478948.aspx) and I'm trying
to plan ahead for multiple applications. As I'm sure many of you are
aware, the schema defines a table for Users, which has an ApplicationId
column for mapping the user back to a specific application
(http://msdn2.microsoft.com/en-us/library/aa478948.asp2prvdr0102l.gif).
I'd like to make use of this capability so that I can have a single
provider database that is separated from each of my applications. The
problem is that in my application databases, I always use the primary
keys within the provider database as the unique identifiers that provide
referential integrity with the "stuff" (please excuse my complex
technical jargon) in my application database. For example, if a user
had a list of bookmarks, I would create a one-to-many relationship
between the Users table and the Bookmark table on Users.UserId &
Bookmark.UserId. If I separate the provider database that stores my
users with the application databases, I don't know how to reliably
enforce referential integrity. And then, of course, it opens up the
dilemma of future expansion and how to deal with joining when the two
databases are located on completely separate database servers.

How did Microsoft intend to deal with this situation? I could obviously
extend the built-in provider classes in the .NET framework to design the
concept of referential integrity; however, this would not guard against
accidental (or intentional) creation of orphaned records by editing the
data manually in the application databases.

Thank you in advance,

--

[quoted text, click to view]

The only way to enforce integrity across databases is by using triggers,
to check to make sure that the "foreign keys" exist when inserting/updating
rows in the child tables, and to make sure that no references exist when
deleting rows in the parent tables. It's really not too difficult to write
one of these triggers:

CREATE TRIGGER EnforceForeignKey
FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS
(
SELECT ParentTableKey
FROM INSERTED

EXCEPT

SELECT ParentTableKey
FROM ParentTable
)
ROLLBACK
END


.... distributing the database across servers dosen't really complicate this
much. You'll just need to create a linked server so that you can do
cross-server checks.


--

Adam Machanic
SQL Server MVP - http://sqlblog.com

Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220