Hi Daniel
First, we have the same considerations here, mostly because of the easy backup and restore of individual databases.
Second, I don't think speed would be an issue if several companies were kept in one database. Somebody please correct me if I'm wrong, but as you probably wouldn't have more than a dozen companies in one database, the company ID could be bitmap indexed, thus very fast to select.
For these reasons, we plan to mix the two approaches, having groups of somewhat related companies (sister, daughter, holding, factoring, etc.) in one database but completely unrelated companies in separate databases.
Further, you may wish to look up the thread on "Custom library namespace" in March.
/gustav
We are trying to re-desing our database solution. Currently we have a setup that is similar to this:
DB1: * Setup information all Companies DB[n]: * Specific information for Company[n]
where all the DB[n]s share the same schema, but differ in the data they hold. The rationale for that was that a company specific DB could grow quite large. If all the Company Specific data was held in one big database for all client companies, a join with the company would always be required, taking a performance hit since some of those companies have quite a bit of data.
Does it make sense in Cache to split data like that? Is it just a much better solution to have the data all in one DB, instead of spread out?
I know I might not be explaining myself very clearly, I could try to explain it in a different way if it would make it easier to understand.
Thanks for your thoughts,
Daniel.
