List:General Discussion« Previous MessageNext Message »
From:Arthur Fuller Date:March 19 2009 2:40pm
Subject:Re: Separate customer databases vs all in one
View as plain text  
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

Thread
Separate customer databases vs all in oneDaevid Vincent18 Mar
  • Re: Separate customer databases vs all in oneArthur Fuller18 Mar
    • Re: Separate customer databases vs all in oneJohnny Withers18 Mar
      • Re: Separate customer databases vs all in oneMorten18 Mar
    • Re: Separate customer databases vs all in oneDaevid Vincent18 Mar
      • Re: Separate customer databases vs all in oneArthur Fuller19 Mar