.NET application design with multiple line-of-business databases

Question

Core company data is held and managed in physically separate, third-party, line-of-business applications: Finance, Transport Management. Customers are created in the Finance app (SQL Server), delivery information is held in the Transport Management app (Oracle). Communication between the two is point-to-point.

We need to build a new application (well upgrade the old one, but essentially from scratch) to process customer claims for damaged or short deliveries. Claims, customer and delivery data is currently manually entered in to MS Access. This will be migrated to a SQL server DB. The app development platform is VS2008 (C#).

I would like to avoid having all of the customer and delivery data in the claims database, since we already hold it elsewhere, so I plan to produce WCF based feeds from the LOB systems (and possibly the claims db) which can then be used as the data sources for the customer claims app. There will be claim-specific data entry but the core customer and delivery data would not need to be updated in the LOB apps.

So far I have in mind

database-->ORM-->WCF \
database-->ORM-->WCF   --->BLL-->UI
database-->ORM-->WCF /

but it feels wrong as I will be creating separate service feeds for Customers, Deliveries and Claims (object-oriented services?). What I also can't quite grasp is how and where I join and work across data sources within the app to produce, say, a report showing claims against deliveries per customer (i.e. where I would traditionally write a query or view to get all of this from multiple tables in one DB).

Am I on the right track or I am missing the big picture here - should I just run regular extracts in to a claims db and work with traditional n-tier / n-layer architecture?

Answer 1

When generating reports, it's usually tolerable to deliver not most up-to-date data, so it may be a good idea to dedicate separate DB as source for reporting quieries. Your master DBs will receive updates from UI (taking advantage of transactions and conflicts detection) and then replicate data to reporting DB.

This architectural pattern is called CQS (Command Query Separation), read this great article by Udi Dahan.

Answer 2

I don't think your design is too far off from where it should be.

If you have apps that will access finance data via the WCF service or the Transport service, those make sense to build. They also make sense to build because each of those services just supports what it needs to know about (ties in with the Single Responsibility Principle).

Where it might not feel right is where your UI app needs to know about and call 3 separate services to get its job done. In situations like that we've often built a wrapper service that makes the call to the appropriate service. Meaning your UI app would reference a WCF service and that service would then call the Finance service or the Transport service or the Claims service. Downside - each call results in multiple calls... yes. But it abstracts the logic away from your UI app and provides the benefit of giving you a place to manipulate or combine data from the other services or to add other business logic that is appropriate for the app. You also still have the benefit of the Finance service still supporting the finance apps without your UI app's business needs getting in the way or muddying up the code for its benefit.

I'm sure that there are different solution paths for this. This is just how we've handled in a couple of applications.

EDIT (answering your follow up question took too much space to make a comment).

If the data you can get from the Transport service is enough to satisfy the question that is asked by saying "getCustomerDelieveries" then no I wouldn't break it out to another wrapper service. If you need more data, then what other apps would also benefit from that service providing more customer information? Do those apps rely soley on the Transport service? This is one of those where the answer has to "feel" right to you, since you know the most about your systems.

Perhaps you need to break the SRP rule and have your Transport service get more customer information from the finance db or service. Or if apps that rely on the Transport service routinely need more customer data then thought could be given to expanding the customer table in the Transport db.

No rule, principle or philosophy should be applied so rigidly that you can't break it if it makes more sense for your app. It's going to be a balance and there is no right or wrong answer, just what works better for this situation.

You started this post by talking about a new UI app that would support the Claims part of your business and it needed both Finance and Transport data (as well as its own). That is a perfect candidate to call a wrapper service. It needs data from 3 distinct and separate data sources. Your Transport service has limited customer information which works well for some apps but perhaps not so well for others. If you write a wrapper service that mirrors your Transport service 100% and additionally provides a bit more customer data, what have you gained? More data for the apps that consume it but also more maintainance for you whenever you add functionality to the Transport service. What other value could this wrapper provide?

In this case, to me, having the Transport service get more customer data from the Finance service "feels" better. Your Transport db has some data but not enough. It's almost like the Transport service needs to make up for this short coming by fufilling the data need itself.

Answer 3

I should use an orchestration service with WWF (or other orchestration tool).

I like this view:

DAL, BLL, SIL --> WCf1 DAL, BLL, SIL --> WCF2

wcf1 and wcf2 are joined by an orchestration service over them. This way the services remains autonomous and decoupled, and you could reuse them in other orchestrations.