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.