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?
is_XXX
field, have a table accessible only to that application calledtblIsXXX
that contains foreign keys to the country table. This way you don't have any copies of the country table. You can easily add countries.