Architectural design for multi customer project with sligthly different database structure (for each customer)

Question

I'm maintaining an application that was copy-pasted for each new customer (...yeah, I know). If a new customer wanted some new functions which the others didn't need, only his code part was changed. The Solution also includes a sort of "common.dll" where all code is put that is the same for all customers.

The underlying MSSQL database for each customer is slightly different. All have more or less the same tables but some customers have for example additional columns or new tables for the custom specific functions.

As "typed datasets" (ADO.NET C# 2.0) are used in the Visual Studio solution for each customer there is always a specific solution (file) and in it a specific typed dataset (eg. "XyDataSet.xsd"). In the solutions I have helper classes eg. for handling orders. One of the methods in this class could be "GetOrder(string orderId)" which uses the customer specific DataSet to retrive an order (as typed DataRow).

As you see I have "GetOrder" methods in each solution for each customer as there might be little differences in the order table like maybe additional columns. So if a change is needed to the general "GetOrder" functionality I have to change it in all projects which is of course a maintenance nightmare.

Do anybody of you see a way in which this "architecture" could be improved? Or maybe with the currently underlying different DB schemes there isn't much to improve in the higher application level?

Answer 1

It is very difficult to answer this question completely, but I would start thinking this way:

Database level:

• Find what is common and have that as a database schema.

• Find what is addition and have that in separate tables, indicating in that table which (from common schema) is the owner of the row and its corresponding value. e.g.

  Orders {OrderID, CustomerID, etc.} (this is owner table)
  OrderExtensions {ExtensionID, ExtensionName, ExtensionValue} (this is extension table for Orders)
  OrderExtensionsToCustomers {CustomerID, ExtensionID} (this shows which extension is available for which customer)

Data-access-layer level:

• Database level design should be implemented here.

Domain-model layer level:

• Some manipulations with features (for UI layer) should move to here.

UI layer level:

• From here something should be moved to database layer, e.g. UI elements which appear only for some customers, think of them as a "feature" (extension) activated for specific customers only, so they are visible only for them.
• Personally I would go farther by abstracting the "feature" term, making it loadable dynamically. This is achievable easily if you are comfortable with using reflection. If this can be achieved, then it will give you the privilege of having a little code in domain layer - not all the code for all customers put together, but loaded dynamically for specific customer upon request when needed.

Final picture:

• Customer accesses the UI interface, in fact the specific page.
• Database queried for UI features available for the current customer (e.g. additional buttons, etc.).
• Database returns definitions of each feature for the customer.
• Each feature is loaded dynamically and added to the page (e.g. to the special placeholders for features).
• When feature needs to act, it passes the request to the domain layer, which in turn loads the "processor" part for the feature, maybe also dynamically, or just finds the implementation which feature needs to call.
• Call is routed to the database and the common information gets saved to common (owner) tables, while additional (extension) information is saved to other (extension) tables.

I know it is a long answer, but I hope this makes sense :-)