Common entities with differing attributes across multiple databases

Question

We have two systems each with their own database. Each system has Parties (Customers and Vendors) and each of these can have one or more Sites (addresses) for different functions (delivery, holding, ...).  Some of the Customers and Vendors appear in both systems. Both systems need to keep track of customer and vendor Licences. A Licence belongs to a Party and applies to a specific Site. So it would make sense to hold the common data (Parties, Sites, Licences) in a shared database to simplify maintenance etc.
However, each system has different attributes for Customer, Vendors and Sites that make sense only in that system.
For example system A holds routing information against each customer that is not relevant to system B. Further, the routing codes are foreign keys to a Routes table that would only exist in A. So it would not make sense to store a Customer's routing info in the common database. (There are many other examples.) So it looks like I will need to maintain entities in all three databases (A, B and Common), and keep the entities in sync - which will kind of cancel out the gains I hoped to make.
So my question is: has anyone come up with a satisfactory way to deal with this kind of thing? Or should I just combine the two systems?

bbaird · Answer

Yes, you absolutely should not store the data across more than one system if it can be avoided.  But welcome to the nightmare many organizations have created for themselves.
Without knowing the exact circumstances of how the applications are set up, it'll be hard to give you a definitive answer, but hopefully we can get you in the right direction.
Recommendations:

This will be incredibly stressful experience, so make sure you have an exercise regimen or therapist lined up.
Now is a great time to make sure your new system for storing customer data complies with GDPR and other data privacy standards!
Store the customer data in a common database, but force the other systems to use the primary keys of that system.  You will either need to update each system's data with the new key (hard at first) or create a mapping from the source system's key to the new key (easy at first, harder as time goes on). It isn't possible to maintain strict PK-FK relations between different databases, so you'll need to ensure procedures exist to keep things in sync if records are merged, deleted, etc.
For attributes not stored by both systems (but not required to be stored in either system), you can create a table in 1:0-1 relation with the master table and store the optional column(s) there.  Depending on the exact circumstances you may need to create subtypes and handle the optional columns to ensure the proper columns are stored for each subtype.

Trying to maintain anything other than the proper keys across each system will lead to far more headaches than you have today.
Other things:

Know your industry's regulatory/reporting requirements and ensure any changes you make won't interfere.
If you don't already standardize information like addresses, you'll want to do so now to make matching easier.
People are really bad at typing consistently, so get familiar with string comparison functions (Levenshtein Distance and Regex).
If you are in a strict environment, or don't have too many records that are close matches, may make sense to have a human review any possible matches before merging records.

Common entities with differing attributes across multiple databases

One Answer

Add your own answers!

Ask a Question