The concern here is a confusion between two alternative methods for storing data. Our product migrates records from any service A to any service B. These services define their own records and the structure of this record is dependent on the service. We store the records that travel through our system so that we can perform "diffs" to verify updating only what's changed.
Customers of ours create these migrations between services in a logical "environment." So each customer can have any number of environments that they can then perform these migrations within the context of.
Our current storage method is that a table for each customer and environment is created dynamically and this table houses all records migrated in this environment (regardless of service). There is more complexity such as columns generated for all the fields and some Reflection in the language that is probably ancillary to this question. This process was chosen to ensure data integrity, such that no record from Cust A Env A would every be pushed into Cust A Env B and vice versa (and cross customer).
The competing design that has been proposed thus far is that we eliminate the need for dynamic tables and use one table for records and add necessary environment and customer keys to the record rows. This prevents a "dual database hit" scenario where we have to first look up table names, then query those tables as we could just simply query and perform a join - or maybe not even have to do that if we have the IDs already. The concern here is the possibility of data accidentally being applied to environments which it does not belong.
What are the pros and cons of both situations, and which will be more efficient (both space and growth wise) when the amount of data stored increases. I see an extremely large number of tables if we continue to spawn tables and each table would most likely hold a few 100,000 records (when relatively small) while one table would grow to severl million or 10's of millions of rows rather quickly which could affect performance of queries negatively where multiple tables may alleviate that concern.
Given that the question is (or was) on hold for being opinion based I would like to make it clear that I'm only interested in the factual pros and cons - not preferences or opinions for either angle.