Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

We have built a service (PHP Based) for a client, and are now looking to offer it to other clients as a hosted service. For this example, think of it like a hosted forum service, where a client signs up on our site, and is given a subdomain or can use their own domain, and the code picks up the domain, checks it against a 'master' users table, and then loads the content as needed.

I'm trying to work out the best way of handling multiple clients.

At the moment I can only think of two options that would work:

  • Option 1 - Have 1 set of database tables, but on each table have a column called 'siteid' - this would mean every query has to check the siteid. This would effectively work with just 1 codebase, and 1 database.

  • Option 2 - Have 1 'master' database with all the core stuff such as the client details and their domain. Then when the systen checks the domain, it pulls the clients database details (username/password/dbname) from a table, and loads a second database. The issue here is security of the mysql server details, however it does have the benefit that they are running their own database instead of sharing one.

Which option would I be better taking here, and why? Ideally I want it to be fairly easy to convert the 'standalone' script to the 'multi-domain' script as we're on a tight deadline.

share|improve this question
    
There is no answer to this. It is called tenancy, global info: en.wikipedia.org/wiki/Multitenancy Depending on your wishes of mostly customisation you can make a choice but it's not possible to give a concrete answer. In general if you take a look at most projects you see big data solutions. So solutions with a single data model. They solve your issues by for example sharding. But, as stated, first make your business case more clear. –  Luc Franken Nov 9 '12 at 19:57
add comment

1 Answer

Option #1 is generally the best option in terms of maintenance. You make changes once, and its immediately applied to all customers. I believe the term for this is 'multi tenant'. The one PHP application I maintain has a couple thousand customer accounts across a number of 'private label' sites all in a single db, and has been running for almost a decade now.

Keep in mind, you dont need to add a 'site id' to every table. Generally you'd only need to keep track of that in a few tables. For example, if you have a users table, and an addresses table that links back to it, you dont need the site id in the addresses table since you'd probably have the site id in the users table.

We dont actually work by 'site', but rather, customer account - the same user works across all virtual 'sites'. But the principle is the same.

There are times when this may not be the most practical - it depends on how much customization is expected. If customers are expecting drastic changes specific to them, at that point it might be better to keep everything separate.

There's also Option #3, which is simply to have config files specific to an individual site, and have that point to a different database. This way you remove the need for a shared 'master' db. If I felt the need to split a system up into multiple db's, this is probably the approach I'd take so as to keep things a bit less complicated by removing the need for a 'master' db.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.