You only confirm my first-blush opinion: keep separate databases. Regarding your second issue (joining across databases for reports), I would suggest that these operations are best done against an OLAP database not the numerous OLTP databases. That is, create an additional database whose sole purpose is reporting, and which accumulates all the data from the numerous OLTP databases, on some scheduled basis (schedule dictated by stakeholders -- how recent should the reports be? Will one week do? One day? One hour?
The basic idea here is that reporting does aggregates and therefore necessarily does table scans, especially in your case. To place this burden on the OLTP databases is an error in design and more important, a bottleneck in performance. My advice would be to separate the reporting tasks from the data-entry tasks. Aggregate the data periodically in the OLAP database and base all your reports on this, not on the OLTP databases. This way you maximize data-entry and update speed, while also maximizing the reporting speed (since it won't cause contention with the data-entry activities). This would mean that the aggregate db is very large, but OTOH interrogating it won't impair the OLTP databases in the slightest. So the big problem this scenario suggests is the granularity of the updates to the OLAP version of the data. That's not for me to decide. Ask the stakeholders how recent the data must be and proceed from there. Arthur
