Multiple application instances on the same database

Question

Hello! I'm writing an application that that I'm going to provide as a service and also as a standalone application. It's written in Zend Framework and uses MySQL.

When providing it as a service I want users to register on my site and have subdomains like customer1.mysite.com, customer2.mysite.com.

I want to have everything in one database, not creating new database for each user.

But now I wonder how to do it better. I came up with two solutions: 1. Have user id in each table and just add it to WHERE clause on each database request. 2. Recreate tables with unique prefix like 'customer1_tablename', 'customer2_tablename'.

Which approach is better? Pros and cons? Is there another way to separate users on the same database?

Leonti

Answer 1

I would stick to keeping all the tables together, otherwise there's barely any point to using a single database. It also means that you could feasibly allow some sort of cross-site interaction down the track. Just make sure you put indexes on the differentiating field (customer_number or whatever), and you should be ok.

If the tables are getting really large and slow, look at table partitioning.

Answer 2

It depends on what you intend to do with the data. If the clients don't share data, segmenting by customer might be better; also, you may get better performance.

On the other hand, having many tables with an identical structure can be a nightmare when you want to alter the structure.

Answer 3

I'd recommend using separate databases for each user. This makes your application easier to code for, and makes MySQL maintenance (migration of single account, account removal and so on.)

The only exception to this rule would be if you need to access data across accounts or share data.

Answer 4

This is called a multi-tenant application and lots of people run them; see

multi tenant tag

For some other peoples' questions