I’m creating a SQLS system that needs to interact with existing data from other databases that share common attributes. To give an example : A system of a coffee shop with a table tblcoffeesales and to retrieve the total amount for a sale the system provides FnSaleTotalwhich performs several operations with its tables for a given SaleId. And also another system of a cinema with a visits table and its own mechanism to retrieve the total amount for a visit. Suppose I need to integrate both systems in a sales reporting system, so I need the total amount from both entries in a general “polymorphic” sale entity. I’m a looking for an scalable, general, approach, since the application I’m working on may to need to interact with other legacy systems later on. A way I have thought was to have a connections table, that stores the parameters to get to a generic view on each system (this implies writing specific views for every system, with whatever specific computations I may need) , and that the views follow a “contract” so that my system assumes certain things on this tables information and can use their entries for its functionality. In this way, when I need a sale report for n systems, I can union on the views specified on the connections table.

Before implementing this approach I’m seeking for wise advice from more experienced SQL programmers, what are your ideas on this topic? Any standard way for this kind of problem? Thanks in advance