We are developing a “middle tier” to replace the existing level of access to business logic / data access. One of the design problems that we are in is that we need to design it in such a way that several client databases and / or mid-level elements can live on the same server as part of our hosted location. The database schema and settings for the hosted environment are currently fairly well established, as it is already in production. Essentially, on a given database server in a hosted environment, each client has an instance of SQL Server that is identified using a unique client identifier.
We are trying to decide whether it is necessary to have a separate path from the client application through the web service, business logic and data access to the database for each client, or to have one, common instance, each part in which the data access level is responsible for obtaining data from the correct instance of SQL Server or somewhere in between. Thanks to a single common path for everything, if any part falls, all customers who access it are dead in the water. On the other hand, with individual paths for each client, is there (apparently) more support, moreover, perhaps too difficult? Here is a terrible picture of ASCII-art from the two options that we are considering:
[Client]--| |--[DB]
[Client]--| |--[DB]
|--> [Web Service] --> [Business Logic] --> [Data Access] ----|
[Client]--| |--[DB]
[Client]--| |--[DB]
Or that:
[Client] --> [Web Service] --> [Business Logic] --> [Data Access] --> [DB]
[Client] --> [Web Service] --> [Business Logic] --> [Data Access] --> [DB]
[Client] --> [Web Service] --> [Business Logic] --> [Data Access] --> [DB]
[Client] --> [Web Service] --> [Business Logic] --> [Data Access] --> [DB]
Which one (or which intermediate option) will be better and why?
source
share