Database architecture change from local to distributed

Question

Our company has a product which relies on local database to work (it allows more client to connect to same database and share data between them).

DBMS: Microsoft SQL Server 2008

Now, we need to create a single database, accessible through internet (i am not interested in the how, for now), which will allow more users to use it as if it is their own.

Simple examples to follow.
By supposing that our program will manage (insert/modify/delete) books and their sellers:

Table Seller:
   IdSeller          PRIMARY
   Name

Table Books:
   IdBook            PRIMARY
   IdSeller          NOT NULL
   Description

Now, we need to distribute it, and categorize data by "Company"

 Table Company:
    IdCompany        PRIMARY
    LicenseNumber

Our idea was to modify primary (??) tables like this:

 Table Seller (NEW VERSION):
    IdSeller         PRIMARY
    IdCompany        NOT NULL
    Name

In this way we are sure Books will belong to specific sellers who will belong to specific companies.
Conceptually this is working, but we will have then to change all the queries made in our DataAccessLayer!

We thought of a couple of solutions:

• company-filtered-views for each primary table
• rewrite all the queries

How would you handle this problem?

Answer 1

You can create a WCF/web service accessible through internet rathaer than exposing SQL Server. WCF/web service can contain all the logic regarding which records to give to the client from database and which to restrict. And your client can simply access the WCF/Web service.

Let me know if I'm not understanding your problem well.

Answer 2

Off the cuff, I would re-do the queries in your DAL.

The problem is, this is a highly situational problem. How frequently do you update this program? Are there major revisions in the work? How equipped is your company to do a large refactoring?

Sometimes, in the business world, hacking a quick solution is optimal :'(

Answer 3

First you need to decide what the entities in this new schema will represent... Is the new seller table the same entity as the old one? or will each row in the new table represent an association of a seller with a company? I.e., can the same seller be with two different companies? Same question for books. The answer will determine how best to modify the table schema to provide the functionality you need.