We have applications that must reach into multiple databases. Previously, we had separate DALs that would reach into only a single database. A separate business layer would sit on top would reach only their particular DAL. Applications (different websites) that would sit on top the business layer were free to call any number of business layers if they needed to share the data.

This worked well for a while. However, nowadays, the solutions to build an application have become massive. The application layers all seem to every business layer. Reuse is happening, but the builds have slowed to a crawl and the amount of unnecessary code that is brought into a single solution seems unreasonable.

Has anyone else dealt with this situation? Did you bring the data sharing down to the DAL later using LLBLGen?

Are you speaking about databases having exactly the same schema?

No, the databases have completely different schemas and tables. They need to share the data and currently they are doing that entirely through code.

Actually, there are also some very nasty triggers and Ruby code that sync data between databases. This is all to facilitate the fact that the data needs to be accessible to the code in a consistent fashion. I think that it makes the most sense to do this in a DAL, but I'm not entirely sure what the best practice is.

If you are sharing data from different databases, then the question is why different schemas in the first place?

It's legacy architecture that we're stuck with. One DBMS is the older system and it's SQL Server, the other is the newer system and it's PostgresDB. However, we have multiple applications that use both databases because they do have different information (and some shared).

So I assume you have some single domain application like "Human Resources" which contains one whole unit of business logic, that's why so feel like you have to merge both DALs. Well, you can do that, you can add two catalogs from different Database engines in LLBLGen (i.e. SQLServer and PostgresDB).