Here’s the situation...
I have 2 different apps that both require a separate shema, or maybe db. There is actually one column in one table of each ot these db/schemas that are in common and a desire to :cross” between them in some cases. For example, an app for keeping track of the census results and another app that keeps track of criminal cases in the justice system. They “shared” field is of course the citizen/defendant. Two different apps that should remain separate, but at times it would be nice to check the legal status of the defendant by looking at the census data.
Considering the somewhat rare query that will need to bridge these 2 data sources (dbs or schemas), what are the pros/cons of having 2 schemas in the same DB vs 2 DBs? What if the query is to be committed to a PLpgsql function/procedure? How awkward is it to bridge schemas vs bridging dbs in that form?
Thanks for any advise/help.
-dave