Building an abstraction database

Question

The Scenario

I have a Classic ASP project with a SQL Server backend that I need to port to ASP.NET MVC.

The legacy site makes liberal use of schema definition analysis in the application itself. For example, to run a given report and let the user supply a sort order, the application will read the schema and provide a list of fields based on that schema which the user can then choose (hey, NOT my design!)

This provides an interesting scenario. The migration to the new application will be done per Fowler's coined "Strangler Application" (we'll build replicated functionality piece by piece and slowly move it over). However, along with this are some requirements for new functionality, which will mean new fields and new tables. Due to this, I'm hesitant to just go throwing new fields in existing tables, because I don't want to screw up anything in the legacy app. Naturally, due to the migration path, we need to be able to use both systems at once for a time.

Additionally, the legacy app's database naming is quite horrid, including any number of reserved words and special characters and so on. Also, some table names are completely non-semantic (for example, their primary "Members" table is in fact named after their first and primary client from some 20 years ago).

My Thoughts

So, we have one database, let's call it LegacyDB This cannot change. Yet we need to add new functionality for the new site. My thought was to create a second database, call it NewDB. This will contain any/all "new" data. I'd maintain a pseudo-1:1 relationship between the tables in LegacyDB and 'NewDB' on each table's ID.

What I'd then like to do is build some sort of abstraction database over both of them that the new application will interact with. This would receive a query like SELECT blah FROM dbo.Members WHERE blah = blah, and would query from both the LegacyDB and any new fields as required from the NewDB. Likewise, all other CRUD operations against the "abstraction" db or "facade" db (for lack of a better term) would similarly work the same. I'm thinking some carefully crafted views could be used to manage this.

This way the existing legacy app could use the LegacyDB without having to do any major reworks to that mess, and the new application would have a nicely-named and new-feature-enabled database to work with without having to manage replications between two separate databases.

Once the legacy app is fully phased out, the LegacyDB and NewDB would be merged and theoretically take place of the abstraction/facade db without requiring application level changes.

Essentially what I'm thinking is top-level views in the facade db that would in turn pull data from the appropriate databases. If the application sends the following query to the facade:

SELECT Field1, Field2, Field3 FROM ThisTable

The facade would have ThisTable defined as a view:

CREATE VIEW ThisTable AS (
    SELECT 
        a.LegacyField1 AS Field1, 
        a.LegacyField2 AS Field2, 
        b.NewField3 AS Field3
    FROM legacyDb.dbo.BadTableName AS a
    INNER JOIN newDb.dbo.GoodTableName AS b ON a.ID = b.ID
);

(for updates, I haven't quite worked out the feasibility - one of the reasons I'm asking here)

My Questions

Is this a crackbrained idea? Has anyone tried it? Is there a common name for this approach (if it's even a common approach). Is there some reference I can find for pros/cons/gotchyas? I'd be quite grateful for some experience to feed off of before I start the undertaking.

Answer 1

_Note:

_{The top part of this answer is an answer to the question before the view-example has been posted by the original poster. For an updated version, taking the view in mind, check the bottom of this answer, under the line.}

---

I don't like you passing a query to a database abstraction layer actually covering two databases.

An abstraction layer dealing with SQL is very low level, architecture wise, and it's responsibility is usually only one: to unite slight differences among database engines (e.g. the LIMIT clausule), internally converting some SQL query into a new one, based on the database engine you are using.

This can be done because the change is usually very simple.

What you are suggesting is actually writing a query, which gets somehow transformed to a different query (suddenly querying different database and table). Yes, this can be done, but you will need a complicated mapping table (I assume you would want to do something like parsing the query, deciding which attributes are present and based on some logic query either one or both of the databases). I can hardly see how that is efficient. You will spend hours writing and debugging this layer, when you could simply write the query directly.

You should decide, where the database layer begins and where it ends. Once you will have done that, I think it would be more feasible start providing an abstraction to the database layer, most likely through interfaces, but these interfaces should know nothing about SQL anymore, they sbould contain simple methods like:

List<Members> LoadMembers(int maxAmount);
void SaveMember(Member memberToBeSaved); 

An interface containing methods like those two mentioned above would be implemented and contain the actual SQL querying both databases, but you shouldn't care about that. What you should care about is getting the actual data, but not how you get it.

But couldn't the class implementing the interface then contain the database abstraction layer which would accept the queries which are transformed to querying either the old or the new database?

Well obviously, it could, but I don't think it's worth it. Not from a business perspective.

What you want is to create an abstraction which would accept the common query. Current implementation of this abstraction would the transform this common query to the special one, querying not one but two databases. In the future you would like to swap out the current implementation of the database with a new one, which wouldn't transform the queries anymore, but would use them directly, because the database would have the format.

There are few problems with that approach:

• Creating a well formed implementation of the transformation will take a long time, a lot longer than just writing the queries to both databases manually now and replace them later when the databases are merged.
• How can you know how the database will actually look like in the future? You cannot. The database may as well change and you will have to rewrite the query anyway, to adopt the new structure. So now you have spent a good amount of money for developing a feature which in the end was not even used.

So while it's obviously possible to have such layer, in my opinion it is not worth it. It's too much work for something that does not bring that much.

---

Now that you have introduced the view, it actually makes more sense. In a way, creating the view is you writing the query manually, as I have suggested, but placing it directly into the database, rather than having it in code.

There really is nothing wrong with that approach. Views across multiple databases are pretty standard, just bear in mind that the performance will be slightly worse than operating on one of them (just as joins are slightly slower than taking data from a single table).

Also with that approach, if you go as far as creating views to unify the two databases together, you should forsee the future and create the view(s) to match the needs in the future, so you do not need to drastically change your SQL queries.

Answer 2

I've decided not to do this. After no little review and proof of concept testing, I've determined that it's just not solid enough on various levels of DML, namely performance, ease of development and interference with other possible triggers.

The main issue that I ran into was trying to optimize for performance. While the main application is mostly single-row writes, there are a number of components that may perform bulk updates as well, and these INSTEAD OF triggers on the views would have to handle all cases the same. This in itself didn't present too much of an issue, until the possibility that an "extension" row might not yet exist, in which case I either needed to a) check for each row on a RBAR basis and run INSERT or UPDATEs accordingly, or use MERGE.

Anything RBAR on this broad of a context is a no-go, and while I don't have any particular qualms with MERGE, I'm also not comfortable using it across the board like this (especially considering that the base tables themselves may have audit-style triggers on them, which MERGE doesn't quite handle as well as it should: https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/)

So, I lack a way to ensure that any given DML operation is going to work nearly as well as the direct DML itself.

The other reason I've decided against this approach is because in many cases it would seem that I'll have to write specific code rather than generic code to handle these. In contrast, my DML audit triggers are very generic and a single procedure is used to "attach" them to a table. Thus if I need something, anywhere, from any table, I also know exactly how it was set up without having to look into the details of the table in question.

With INSTEAD OF triggers on the facade views, I cannot do that. Each table/view pair may have naming conversions or other specific oddities that are accounted for. Moving forward into primary application/component development, I can see this as easily becoming a major maintenance nightmare.

All in all, if I could have not had to worry about performance/merge interference and come up with a set of generic triggers and handled it as "added a table, create the view and run this sproc to generate the triggers, or added a field, run the sproc to drop and recreate the triggers", I might have done so, but alas, not in this case.

I'll likely resort to an interfaced layer and domain model mappings for the OOP stuff, which is where the bulk of the application development will be done.