Database Object That Reads From Two Tables and Writes to One

Question

I am updating a database design that must be compatible with old and new applications. My goal is to make a design change which minimizes impact on the existing code.

The Design Change

Currently our database has universal data that is stored in each customer database. I want to move this common data out to a different DB/server to make maintenance easier. However, I also need to retain a local copy of these tables so customers can add and edit custom items (which we will not be responsible for maintaining).

What to Use?

So basically I need selects to return data from both tables and update/delete/insert statements to modify data from the local table.

I think I can use a view to return data from both of the tables as an aggregate, but is it possible to set it up to write to only the local table?

I'm using SQL Server 2008 with VB.NET.

Answer 1

Yes, you can use views to obtain an aggregate representation of rows from two (or more) tables. On the other hand, as you seem to already realize, views are not normally writable. Even if MSSQL (or any other RDBMS) offers some magic features to write to a view, I would advise against using them, because magic tends to turn and bite you in the end. (Magic works very nicely until it doesn't. And it makes it very difficult to understand what is going on.)

The vast majority of database operations performed by any application are reads, so you can cover those with views. For the remaining very small percentage of operations that are writes, just do it the straightforward way and modify the code to write to the right tables that are meant to be written.

However, keep in mind that breaking a database down into separate databases is hardly ever a good idea, because transactionality and referential integrity are usually lost when you do that. Are you sure what you need cannot be accomplished by adding one more column which identifies whether a given row belongs to a specific customer or to the universe?