I've built this web app (php & mysql) which stores information for various organisations (about 20 clients currently).
The current scenario stores client-related information in individual databases, so there's 20 client databases and 1 master database.
One of the main advantages here is that as each client db is isolated, the numbering of client artifacts (reports, audits) etc. is sequenced; giving our clients a feeling of security.
Each DB has roughly 15 tables, and the most rows in a table are about 2000. This is expected to be bumped upto 5000 records, at most.
Managing a single db-level change means changing 20 databases, but in the rare event that I need to make such a change, I use a script that does this in a single function call.
We're on a shared hosting arrangement, and our ISP supplies us with a limited no. of databases; and that's what led me to think in terms of centralising the database; so that ALL the client data can be stored in the master database.
Of course, some important issues that crop up are:
a. Maintaining the artifact sequence, (this could be addressed by creating an additional reference key) b. Speed and performance (in which case I can create indexes to speed things up) c. Security: This will be managed as each query that fetches client info. will also track their client_id
In the future, we might need to consider comparing datasets of one organisation with another, but I believe that can be achieved on a centralised db too. I'm somewhat inclined (for performance and maintainability reasons) to move to a centralised database.
Do you think moving to a centralised database makes more sense than staying as we are (on individual databases)?
Thanks for your advice.