How to manage shared vs customizable tables

Question

We have many clients using our application, and each of them has their own, identically structured database on our MS SQL Server. We also have a central database for information that is invariant and therefore shared among all the clients, such as the USPS ZIP Code database. Each client database has a view to these tables, like so:

create view V_ZIPCode as
select ID, ZIP, City, State
from SharedDB..ZIPCode

The clients do not have rights to modify data in SharedDB.

There are, however, some tables where the data will largely be shared - but the clients may want to add a few records of their own.

So I'm wondering how best to implement this situation.

I could just put the table on the client database and pre-populate each client with the shared records - but that seems way too heavy-handed and inelegant. And of course, if the shared records are updated for whatever reason, the changes won't propagate to the clients automatically; I'll have to copy the data over to all the client dbs, which is very nasty.

Another idea I had was to create identical tables in SharedDB and the client DB, then create a view on the client DB like so:

create view V_MyTable as
select ID, Description, convert(bit, 0) IsClientData from SharedDB..MyTable
union
select ID, Description, 1 from MyTable

In order to prevent ID duplication, I could seed the identity value on the client tables at a very high number like 1,000,000 (way more than I'd ever use in the central DB; these are pretty stable lookup values); I'm not sure if I'd need that IsClientData field at all, but that's a minor detail. The client would be able to select whatever they want using the view, but they'd only be able to modify data on the table in their own database.

This approach has a couple of drawbacks that I can see: for one thing, I wouldn't be able to have foreign keys on any table that references this view - but I guess that's the case with any reference to a different database. The other is that I'm not sure how efficient this view would be in a query.

Can you see any other problems that this approach would introduce? Can you recommend any optimizations? Or would you recommend a different approach altogether?

If it makes any difference, the business layer of the application is written in C# using Linq-to-Sql.

Answer 1

Barring (as you said) spreading data across the system, it seems pretty solid to me.

Here's an outline of a way to store all the data in one central location. You'd have to flesh this out some, work out the naming conventions and updatable view wrinkles and whatnot. I think it's elegant, but elegance can be overrated.

--  In SharedDB
CREATE TABLE CentralTable
 (
   Id              int           not null  identity(1000000, 1)
  ,OwningClientDB  sysname       null
  ,YourDataHere    varchar(100)  not null
  --  Toss in stuff like who added it, when it was added, etc.)
 )

As you mentioned, all common data gets added via SET IDENTITY_INSERT CentralTable ON with Id values under 1,000,000, and with Owning ClientDB as null. Then, in each client DB:

--  In client DB
CREATE VIEW vCentralTable (Id, OwningClientDB, YourDataHere, etc.) as
 select Id, OwningClientDB, YourDataHere, etc.
  from SharedDB.dbo.CentralTable
  where isnull(OwningClientDB, db_name()) = db_name()

The view filters out which rows a given client can see. I based the filter on database name, but there may be more efficient ways of doing this, depending on how you identify "owning" clients.