I'm mainly interested in opinions on the trade-offs between having a single central server all the websites connect to as opposed to each website mirroring a subset of the master database with all the products in it. For example, will I run into severe performance issues (or even security issues, or restrictions) making queries to an offsite database? Will we hit scalability issues we can't handle early on from the sheer bandwidth required to maintain this? If we do go with something like a script that keeps smaller databases (each containing a subset of the central master data) in sync, what sorts of issues will we likely encounter there? I would really like the opinions of people far more knowledgeable than I am regarding the pros and cons of both setups and what headaches we are likely to encounter.

CLARIFICATION: This should not be viewed as a question about whether we should implement one database vs multiple databases. This question has been answered numerous times. The question is regarding the pros and cons for a deployment like this having the ability to manage all the websites centrally (one server) vs trying to keep them all in sync if they each have their own db (multiple servers).

REAL-WORLD EXAMPLE: We are a t-shirt company, and we have individual websites for our different kinds of t-shirts, but we're looking at a central order management integrated with our single shopping cart (which is ColdFusion + MySQL). Now, let's say we have a t-shirt that's on 10 of our websites and we change an image for it. Ideally we would change that in one place and the change would propagate, but how would we set this up?

closed as not a real question by gnat, Jarrod Roberson, GrandmasterB, ChrisF Jun 15 '12 at 21:39

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Edited question to remove some "backstory" and hopefully just leave the meat of it. Will provide additional info if requested. – user56874 Jun 15 '12 at 19:42
  • The back story is what scared me away from answering this. It seems you need to do have a "come to jesus" in terms of managing multi-source data remotely. – Johnny Rotten Jun 15 '12 at 19:56
  • Can you elaborate? I'm not sure what you mean by the "come to jesus" comment? – user56874 Jun 15 '12 at 20:00
  • Updated my question as it was unclear – user56874 Jun 15 '12 at 20:14

It sounds like what you are asking about is database replication. You'd set that up with your MySQL servers to replicate changes made to, say, a master product table over to product tables in the various databases for the individual sites. Such replication can be done between databases on either the same physical server or different ones.

That said, doing so seems needlessly complex when you can just use one shared db for all the stores and just have the individual stores filter on those products it can display. A master inventory control panel could be made to assign products to sites. If traffic then becomes an issue, you can replicate the single master database to other database servers, and point read-only queries over to the replicated copy of the database (read from many, but only write to the master db).

  • YES! This is getting at what I'm talking about, and that's precisely what I'm trying to decide. (Edit: Sorry, I just reread your answer 2-3 times and it's sinking in.) How would you suggest managing the "read-only" queries? What kind of setup would we be talking about to manage traffic issues as this thing grows? – user56874 Jun 15 '12 at 20:41
  • And if the shared db is in a cluster/failover setup, you don't need to worry much about database failure taking down the whole business. – DaveE Jun 15 '12 at 20:46
  • You might want to look into a decent vps account, rather than shared hosting. That will give you a lot more flexibility. For example, you are very unlikely to be able to use replication in a shared hosting environment. Regarding outages, if you use just one database but replicate it to a second copy, if the database goes down (which it really shouldnt do very often), you 'swap' the copy for the master. – GrandmasterB Jun 15 '12 at 20:48
  • for handling read-only queries, its pretty easy. Rather than storing the configuration for one database, store it for more than one. For 'intensive' queries, just specifically execute them against one of the copies. But again, you really wouldnt need to do this until you got to the point where the traffic was very high (though I'd still mirror the database itself as a live backup). Anyways, look up 'replication' in the MySQL docs so you can get an idea of how it works. – GrandmasterB Jun 15 '12 at 20:53
  • Wouldn't 100 websites each with their own database consume the same amount of traffic as 100 different frontends for 1 database? – Pieter B Jun 15 '12 at 21:12

The answer is for each Web app you need a different database because of the security reasons. Simply do not put all your eggs in one basket!. If somebody attach your DB then you lost everything.

There is no cost difference between 1 database or 100 databases!. Most of the hosting companies get a certain rate (something like <40$) so you will have unlimited data storage and the ability to create unlimited number of DBs.

  • I'd like to steer away from answers like this (and I don't blame you for answering this way, but I'd like to take the opportunity to clarify a difference between this answer and what I'm getting at with the question). I can have 100 databases on a single server, or spread out across 100 servers. The question is do I take advantage of being able to centrally manage things from one place while simultaneously risking one site going down making ALL the sites go down. – user56874 Jun 15 '12 at 20:07
  • In other words, the question is not "how many databases should I create?" or "should I have one vs multiple databases?" These have been answered numerous times. The question is for a company my size, what are the pros and cons of having a single server that all the websites connect to vs trying to manage them all while they have their own db's. – user56874 Jun 15 '12 at 20:10
  • To clarify you mean 100 websites on 100 different servers? – Pieter B Jun 15 '12 at 21:20
  • @PieterB It is essentially 1 physical server. The admin of the server creates 100 different hosting packages. Each hosting package has its own username and password and its database. – asghar ashgari Jun 16 '12 at 17:52