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

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.

开发者_JAVA百科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?

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 :-)

What about using one common method (in a shared assembly or in a service) that is returning untyped dataset? Then you can have methods in each solution that puts the data returned from this method into typed datasets. That way you can keep the logic for interacting with the database in one place.

Firstly, ouch. Tough gig.

There are lots of things you could do to move to a better architecture - it kinda depends on how much time you are allowed to spend on it. I'd suggest reading "Refactoring to Patterns" by Kerievsky for ideas.

To answer your specific question - I'd consider the "template method" pattern. Broadly speaking, you define getOrder as a series of steps, and allow subclasses to override those steps as required. http://www.dofactory.com/Patterns/PatternTemplate.aspx

I agree with Ole that one of the things you will need to do is sort out the return types - that will make life a lot easier when it comes to refactoring.

I'd also suggest investing in unit tests if you haven't already done so.

Create a typed dataset that contains all common colums of the tables but not the specialised ones. In the corresponding xsd-file for each table addd a

  <xs:anyAttribute />

This will allow to load columns into a datatable that are not defined in the xsd-dataset.

The common colums can be accessed via the typed dataset, the customer specific colums can be accessed through their name or you can create custom specific extension-methods to get the additional colums.

I am not shure if vs2008/vs2010 dataset-editor can handle this or even destroys the anyAttribute information. But the dotnet xsd.exe codegenerator supports anyAttribute since dotnet 1.0 including current dotnet 4.0