Where to put the Country Table?

Question

It might sounds like a silly question but here is my problem.

We have 100s of databases and in each of those databases, there is a country table. It was ok this way but we ran into minor problems at some point like : the different tables were using different country code (2 letters, 3 letters), some country code were incorrects for some reason or some database were lagging behind (yes country change name so you need to update your database).

So, at some point, we decided to put only one country table in a "reference" database. This way the country table would always be up to date and it would be the same for everyone. Anyway, all those country tables were exactly the same right?

The only issue now is that each database is used by a different application and each application has some logic related to the countries. For example, if the country is XXX do that and the way we did it before was by adding a column in the country table "is_XXX". Since we decided to combine the country table, we would now have 100s of columns "is_XXX" in the new country table and each of these columns is most likely only useful to 1 specific application.

So, should we have 1 country table referenced by everything else or 1 country table per database?

Answer 1

The answer may be simpler than you might have originally thought: replace the "Countries" table shared across monolithic database-centric apps with a microservice, or simple webservice that exposes the same functionality with a simple REST call (note: I'm suggesting REST though that is not your only option, though it is probably the lightest-weight option).

Your country example is a perfect opportunity to pull some of the "database" centric logic of your applications out into a simple pattern of a webservice. I think your gut reaction to having the same table exist in each schema correctly throws off the DRY alert - you're literally repeating yourself not only in terms of the data schema, creation, maintenance etc of the data layer (as small as it may be) but also in terms of the data access logic as well.

There are some significant advantages of this approach and a few disadvantages as well to consider before making your final decision.

Disadvantages:

1. Microservices and Maintenance: Obviously maintaining the production and staging implementations of a service-based system is going to be different and more than likely more costly at first than what your team is already familiar with in terms of database-centric solutions. Plus there's the whole "versioning" bit:

   Once a microservice makes an endpoint public, subsequent development must pay the cost of either supporting multiple versions of services or the deployment of services in sync. Microservices, in this sense, increase the maintenance cost of a software system by increasing the number of published API endpoints.

2. The above article also correctly points out that this specific approach may introduce the complexity of advanced distributed systems to your applications where none perviously existed.

3. You also will need to introduce support for some additional assumptions of network connectivity into your applications that may only have a limited built-in assumption of client-server (app + database) in their current incarnations. This also presumes management of time synchronization at some level, at least with respect to deploying changes and supporting multiple versions for backwards compatibility.

4. Performance is also another concern. Although in general smaller microservices can be more easily optimized, the mechanism they employ for delivering their data via web services is different than the mechanisms you currently use for your client server apps and require a different skill set and set of techniques to optimize. It can be done, but it may increase the complexity or delivery time at least on your first iteration.

Advantages:

1. Microservices are here to stay. Monoliths aren't exactly going anywhere, but the trend is towards smaller monoliths not larger ones. You might as well start somewhere easy both for the developers to learn and also for the business to assume the least risk with the easiest rewards.

2. Decomposing into Microservices make the app easier to Test and maintain over time, even if complexity increases. That complexity is easier to manage via automation than the monoliths are - it's a tradeoff. You have to master automation, but there are so many benefits to that process that it hardly seems like a negative at this point.

3. Speaking of automation, deployments become more easy as well. So does maintaining multiple synchronized environments and dev / test / staging environments that are exact replicas of production. The added benefit of not having to deal with "it works on my system" type of issue is one nice extra but so is the entire DevOps culture that you can begin developing in and around your system now. Even Martin Fowler agrees!

4. I also agree with this post that suggests that the microservice approach more closely aligns with both business objectives and capabilities as well as reducing redundancy at the expense of a little more complexity. Keep in mind that build, testing, and deployments will tend to occur more quickly with this model and the efficiencies will likely begin to pay for the extra complexity introduced in no time at all.

5. As the number of microservices grows so does the overall complexity and management costs of your app. This is undoubtedly true, but it is actually a perfectly good justification for starting out with a single microservice, mastering the skills needed to develop, integrate, and maintain one in your stack and then expanding from there as opposed to a true reason to reject the approach.

Answer 2

Both

Have a central controlled table with:
ID int PK
Code varchar

Then let app have table with:
ID PK FK to central
add as many flag columns as they want

This way you still have one column for Code

Another option is an app table
CountryID
FlagID
Value

This way your columns become rows
Problem you have here is rewrite a lot of queries