Multiple application instances on the same database

Question

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.