Best approach to the architecture of integrating two separate databases?

Question

I came across the following questions at work, and I do not have the experience or knowledge to answer them, I hope that some of you are the wisest people can point me in the right direction, any answers will be very grateful!

Scenario

We have two aspects of business using separate databases, human resources and operational areas (Homecare).
Human resources track company employees, shift patterns, absence, payment, etc. Homecare keeps track of customer information, home visits, dates of visits, and the person in charge.

These two systems are separate and are currently in the process of finding ways to integrate them.

In addition, we looked at how to organize our code, which examines these two databases, into reusable, organized libraries.

We have three applications that reuse HumanResources.dll, which are responsible for communication with the context of EF 4 objects contained in the library. The context of the object is almost a mirror image of the database in its current form.

Questions

---

It was going to add a fourth application that will use the data in the HR database.

We:

Create a new EF data model responsible for providing information that only the application is required, while duplicating some common objects such as Employee.

OR

Add new entities / tables to an already large model and accept it's going to become big.

---

In the longer term, we need to join the Shift Pattern information in the HR database for client visits in the database of operating areas (Homecare) in the 5th application.

We got an idea of ​​what we can do; weve come up with the following:

Create a layer that is between the Context of the HumanResources object and the Context of the Homecare object, responsible for combining the two datasets together.

Are there any other approaches that would benefit us?

Answer 1

Add "Facade"

Facade is basically an adapter for a complex subsystem. Since you have two subsystems, I would recommend creating three classes with the following functionality:

• HumanResourcesFacade : A class that wraps all Human Resources functions. The purpose of this class is to expose the methods that each Unit of Work that the Human Resources application is responsible for without exposing the client to any information about the Human Resources application.

• HomecareFacade : A class that wraps all the features of the Homecare. The purpose of this class is to expose the methods that each Unit of Work that the Homecare application responds to without providing the customer with information about the Homecare database.

• ApplicationFacade : a class that wraps both HumanResourcesFacade and HomecareFacade , and provides its clients with public methods that do not require knowledge of the internal workings of one of the two nested facades. The objective of this class is to know: (a) which of the two nested facades is responsible for each client call, (b) make the client call to ApplicationFacade by calling the appropriate method on the nested Façade, and (c) translate the data received from the nested facade , in a format that can be used by the client and does not depend on the data formats of the embedded facade.

I would recommend using the POCO object model to create a common intra-code representation of data that is independent of the actual implementation of persistence. The domain model methodology proposed by Adrian K. is a good approach, but if you are not familiar with the templates and methodology, they can be very confusing and take much longer than methods that are more intuitive. An alternative is simply to use data objects and a Data Mapper . The data mapper basically takes data from the data source and turns it into an object that is independent of the data source or the mapper object. I have included the link below.

One thing I would like to clarify is that although I said that ApplicationFacade has three tasks, I do not advise you to violate the principle of single responsibility . I do not mean that the class should do all these three things on its own, but it should encapsulate any mechanism that you decide to use to complete this process, and that no other parts of the application should address these problems from outside ApplicationFacade . For example, your business objects should not know from which data source they were created - this information should not be accessible anywhere, except what was encapsulated by the ApplicationFacade class.

Reference Articles

• Martin Fowler on application facades
• Martin Fowler on Data Mappers
• A free but complete introduction to Domain Driven Design

Answer 2

Sounds like you need to do some serious data modeling.

You definitely need it for a long time so that you do not get into a serious fight. (if there is one thing that will have a significant impact on your ability to maintain / expand systems and support business growth - this is data management). The good information about (business data) is that your business stakeholders (or should) be well aware of this and be appropriately motivated to support you. The value of such an exercise will bring easy to sell. Having some of them in place in the short term will also help.

The data sources supplied with the product packages (Commercial Off The Shelf - COTS) will not be available for change without jeopardizing these systems, but this does not mean that you cannot use ETL and other databases to create data marts that integrate disparate data. With this approach, it will be important to model data and compare data between systems, but also time.

You will have more flexibility in your own applications - but you may need to resist tactical changes if you do not have very good reasons, otherwise you may have to redo them anyway.

As part of this exercise, you will want to consider the Recording System for each piece of data - where did it come from? Who does this belong to? You can start at a high level by creating a conceptual data model, this is likely to be more related to logical data sets than to specific “columns”.

Use this information for further decisions.

From the point of view of your immediate approach (and your question): in general terms, he will think about laying a layer of abstraction between your systems and data so that applications do not soften from changes when this happens.

Create a new EF data model that is responsible for providing information that is only needed by the application, while duplicating some common objects, such as Employee.

The big problem with duplication — getting data to a state that is dirty — is a “real” record. It can easily kill you. What are the benefits of this approach in your context? Could you do this in terms of support? Ease of development?

Answer 3

It depends on what you mean by integration.

• If you just want to combine different tables for reporting purposes, then you should look at some process to extract and load the selected data from each system into the Datawarehouse. You will need to define a common data model for both systems. This data can then be used for reporting.
• If you want one system to call services or retrieve data from another system, I would recommend that you use the classic SOA template. Provide features that you want to make available to another system, such as services via SOAP, REST, or similar. And force client systems to use these methods and only these methods to send or receive data.

Avoid, if at all possible, by looking directly at the database of foreign systems, replicating data from one system to another, or sending direct API calls to the source system. The guiding principle should be "if I replaced the X system with a SuperX system, how easy it would be to support other systems."

Answer 4

Since you are looking for a long-term solution and business infrastructure, I recommend you switch to LDAP . Read.