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

2 Answers 2

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

Before you decide how to organize your database tables for multiple clients ("the great multi-tenant vs. multi-instance debate"), you should consider how you intend to:

  • scale your service as your number of clients/branded sites grows,
  • scale your service as the number of users grows (either for individual clients/sites, or in aggregate),
  • keep your site(s) available should some of your hardware fail.

These decisions about how you're going to scale performance and maintain availability will make a big difference in how your data should be structured. Key variables include:

  • How much data you will have,
  • How rapidly your service might grow (in total number of clients, total number of users, or other metrics),
  • How much service availability you or your clients may demand,
  • How much isolation and security your clients may desire,
  • What type of platforms you wish to run these services on (in-house, virtualized, cloud, or a combination), and
  • What skills you have on-hand or could readily retain to help you through the development

I have successfully scaled services "up" by simply buying larger server, storage, and network instances. While this will not last you forever, scaling up is a legitimate entry strategy requiring fewer skills and simpler data structures. Eventually you would not be able to economically purchase larger instances, and would need to tackle the more difficult scale "out" direction, hosting your service across multiple cooperating nodes. This can provide much better scalability and availability, but also more complexity and oversight. A combination of clustering, sharding, and replication is usually required.

One intermediate step that can help bridge the gap between the straightforward vertical scaling and "full" horizontal/distributed scaling is scale-out multi-instance. This hybrid approach spreads your workload over multiple nodes, but with a largely vertical ("up") scaling strategy per node. It requires more facility with load balancers, DNS mapping, and provisioning/orchestration than typical scale-up deployments, but not the full "be the master of distributed algorithms and paradigms" that a Twitter, Facebook, or Google-style service design would. It also would map nicely to your "private brand" business style.

That you're on a tight deadline and that you (based on your question) do not have a lot of scale-out/sharding experience both argue for the simpler Option #1 or grandmasterb's Option #3, along with a plan to scale vertically first, then to a multi-instance model.

share|improve this answer

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.