It's not uncommon to have several software systems using the same physical database, but different subsets of tables from it. In such situations, it's still possible to have versioned database approach. In short, each software system should have its own versioning in place. I will go into details below.
I will be assuming that the number of systems using the same database is equal to 2. Similar logic can be used to explain this approach for more than two systems working with the same database.
Since the two systems are separate, they most likely have separate roadmaps and release plans. Because of that, you cannot say for sure which of them will be released first.
This brings the necessity to think about the very first step during release of the database - which is creation of the database itself. Which software system is responsible for creating the database?
This question only applies if the database did not exist in the past (before switching to the versioning database approach). If it existed before, and you are just trying to apply principles of versioned databases, this problem does not apply to your case. If the problem applies to you, let's discuss the solution below.
If you want to be on the safe side, have database creation as a conditional pre-deployment step for both systems. Condition to execute this step depends on whether the database already exists or not. If it exists, we assume that somebody else (one of the other systems) did it earlier, and we skip the database creation step.
In some cases, database creation is a manual step executed by the DBA prior to the release process, which can eliminate the need for this step altogether.
Two different software systems are probably developed in isolation from each-other. That means they evolve separately into new version numbers. Thus, version 220.127.116.11 for the first system is not the same as version 18.104.22.168 for the second system. These two deliver different software solutions at different versions, they just happen to sound similarly.
Because of this, we cannot base both systems' databases on the same versioning history. I talked about VersionHistory table when I first touched the versioned databases topic. That table alone is not capable of solving this problem.
Theoretically, VersionHistory table could still hold both version numbers in different rows by differentiating them with the dedicated column that holds the referring system's name. In practice, this solution will not work properly, since we already know that the two systems are isolated; and, as we mentioned above, we can't say "who goes first". So, who would create the VersionHistory table? who would drop it in case of the rollback procedure?
To avoid complications, simply have two different tables holding versions for different software systems.
This, in turn, raises another concern of naming conflicts in table names between the two systems (e.g. both systems having VersionHistory table). To avoid conflicts, you can prefix the tables owned by the system with letters that identify the system they belong to. Alternatively, if your database supports it, you can have different database schemas (such as "dbo" in MS SQL) for each system targeting the same database.
Chosen conflict resolution technique should be applied to all tables including VersionHistory tables.
While the database is shared, the systems are still different. And since at any point, we cannot assume about particular version of each system's database deployed, we cannot assume its existence at all.
That leads to the rule of thumb to ignore the existence of the other part of the database at all. The two systems share the same database, but they should forget about this fact.
Database is just a destination for the database schema installation and nothing more. If you still want to read data that's contained in the other (not belonging to your system) part of the database, do it without reading the database. I will cover that part below.
This might be a little bit outside of the scope of this topic, but integration (as it always does) should happen between the software systems, not through databases.
The reason for that is, the database schema (even though developed version-by-version), does not really support different versions at the same time. So, you cannot connect to the database schema version 1.0 and retrieve data using that schema, if the database was upgraded to version 2.0. Thus, the version upgrade is governed by the release cycles of the owning system, leading to the broken software systems that just depend on the schema, without owning it.
On the other hand, the system can expose two different versions of the service endpoint, serving in two different ways at the same time. e.g. your software can be consuming endpoint located at http://othersystem.service/version1/calculator, while somebody else is consuming a newer version of the similar service located at http://othersystem.service/version2/calculator. Now, it's your decision to switch to the newer version of the service, once you are ready to do so. You don't have that same comfort level with the databases - they change when their owners say so.
With the recent technology shifts towards messaging systems, it is easy to think of database as another kind of transport between different systems. And it indeed, can be used as a transport, but at your own risk. You should always prefer integration based on the [more] stable service contracts.
That's all you need to know when implementing versioned database approach for the databases shared by different software systems.
The author of the above content is Tengiz Tutisani.
If you agree with the provided thoughts and want to learn more, here are a couple of suggestions: