Multiple Databases with Zend

Question

Hey there,

I'm building an application (using the zend framework) where we will have multiple clients who login and use the application, and each of these clients will be storing lots of data about their users (I'm using MySQL btw).

Basically I'm wondering 2 things:

1. Is having multiple databases, one for each client (ie. ipd_client_CLIENTNAME) with identical tables a stupid idea? Or will it actually be more responsive (than putting everything in one database) if we have 50 clients with 20,000 users' data in 'their' database? I've already managed to build the same system but all on one database, my model classes simply grab the name of the client logged in (in the model classes' init() method) and then dynamically change the name of the table they grab data from, is this going to be just fine?

2. If the "every client on the system gets their own database" makes sense, how exactly would I dynamically change what database my db models connect to (based on which client is logged in)?

In case this made no sense, here's an example of what the databases would look like in the two different scenarios (given 2 clients registered on the system):

Multiple Databases:

ClientA has a database called "ipd_clients_ClientA" with tables "users", "lists".

ClientB has a database called "ipd_clients_ClientB" with tables "users","lists".

Single Database:

ClientA has tables called "users_ClientA", "lists_ClientA".

ClientB has tables called "users_ClientB", "lists_ClientB".

Hopefully that makes sense.

Any help would be GREATLY appreciated, thanks!

Answer 1

Hi,

i would use Solution 2, with multiple Tables for your clients in combination mit MySQL Master / Slave Replication. MySQL Master Slave

If you want to use different Databases, you can use native functions of Zend Framework: Tutorial multipe dbs

And than select the one for the client by some kind of prefix.

Answer 2

Although you accepted that answer, I beg to differ. I think having multiple database would be much easier to manage.

The way that tutorial uses multiple databases is far more complicated than it needs to be. The way I do it is by simply defining the client name in a root file (index.php?) and then my bootstrap uses this constant to load in the correct ini file.

If you start with the separation, you can then go on to using the same principles for caching data in separate structures far more easily.

Using separate databases would make it much easier to manage individual clients.

Answer 3

While ZF does have support for multiple database connections, I would not use them in this particular case.

It makes more sense to me to store a client_id (or site_id) in each of your data tables and use that to filter. Duplication makes it very hard to make application-wide changes. Whenever I see dynamically named tables like that, I usually think it's poor design.

For caching, just prefix any cache keys with the client identifier.