We have several independent databases that have data and code in common, not in the sense that it is accessed between the databases but in the sense that the data means the same thing in each database, and the code does the same thing.
- Configuration settings (e.g. error codes 50xxx, boilerplate text for company name, etc).
- Procedures and functions that perform common tasks (e.g. converting a CSV string into a table, logging an error, formatting an error message based on the error code).
- Table structures (e.g. table for database version history, table for logging errors). As well as the columns, constraints, and triggers there are also common procedures and functions that read/write the data.
- Look-up tables (e.g. date look-up table containing dates between 2000-2100). These are similar to table structures but quite often the data will be the same across the databases. In the case of the date table, the start and end dates are configuration settings, which are read by a function, then the date data is generated by a procedure. All these operations are common between the databases.
- User defined types (e.g. types for passing tables to functions).
For maintenance and support reasons I think it makes sense for things like error codes, procedures, functions, and types to have a "single point of truth" across all the databases, not a different truth in each database.
At the moment each database has it's own copy of everything, including source repository, and we maintain them all independently. This is far from ideal because it's too easy to fix a procedure in A and forget to put it in B, or add an error code to A and the same one to B but they mean different things, etc.
The databases are not updated at the same time and they don't necessarily reside on the same hardware. There are cases where they can read data from each other (if the other one exists).
Is there a standard way of having a single point of truth for data/code that is used across more than one database?