Multiple Databases

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
acidHL
Forum Commoner
Posts: 41
Joined: Wed Dec 07, 2005 7:38 am

Multiple Databases

Post by acidHL »

A client has asked us to design a large system that will allow the creation of multiple copies of an e-commerce site from one central system.

Basically with each site created (up to 1000) a new set of database tables will be created. All the site will be run from one copy of the site, the copy being used would be determined by wildcard DNS.

My question is, is there an advantage to having a seperate database for each site over running one database with many tables :?:
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

I would guess the advantages could be associated with A) less overhead per DB seeing as they are all broken apart (load balanace, sort of) and B) if the one DB goes down, gets compromised or corrupted, it only affects the one DB without screwing up the others.

I am sure there are more benefits to splitting up the DB's, but these two seem to me to be the most sensible and logical, at least from an admin and maintenance perspective.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

That doesn't make any sense to me. MySQL has the ability to add slave servers for a situation like this. Sounds like someone is confused somewhere. Course it could just be me. :wink:
printf
Forum Contributor
Posts: 173
Joined: Wed Jan 12, 2005 5:24 pm

Post by printf »

In this case one database is just as good as running many. Like any application that contains the same data structure it is better to have one central database. Like your example, say you have 10 forums, each run the same software, so your telling me, you are going to have 10 different admin panels and your application will use 10 different databases. If you are, I hope your not the administrators, what a nightmare. Why not just use one and setup your all your programs to access that one database. Your database can handle this, it's the script that maybe can't!

pif!
acidHL
Forum Commoner
Posts: 41
Joined: Wed Dec 07, 2005 7:38 am

Post by acidHL »

Perhaps it would make a little more sense if I illustrated what I mean a bit better...

Code: Select all

MySQL
   ------- Database
                     |------------ sitea_com_configtable
                     |------------ sitea_com_othertable
                     |------------ siteb_com_configtable
                     |------------ siteb_com_othertable
                     |------------ somewebsitec_com_configtable
                     |------------ somewebsitec_com_othertable
vs

Code: Select all

MySQL
   ------- sitea_com
   |                 |------------ configtable
   |                 |------------ othertable
   |
   |
   ------- siteb_com
   |                 |------------ configtable
   |                 |------------ othertable
   |
   ------- somewebsitec_com
                     |------------ configtable
                     |------------ othertable
Would either have a performance benefit over the other?
All the sites would point to the same web root, the PHP would then work out what site is being asked for and pick the right database. The database would hold allt he styling information etc.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I'd personally go with separate databases in this case. Why? It's more modular and categorized.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

I agree with feyd... Each website/organization would have it's own database...

Instead of rewriting all queries to FROM $company_table your sites only need to change the databasename.... And can all the same FROM table queries...
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

I agree with Feyd and timvw. 1) They are very smart guys, 2) their ideas make a lot of sense and 3) I had already suggested separate databases :wink: .
Post Reply