Page 1 of 1
Multiple Databases
Posted: Fri Jun 23, 2006 10:59 am
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

Posted: Fri Jun 23, 2006 5:28 pm
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.
Posted: Fri Jun 23, 2006 6:49 pm
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.

Posted: Fri Jun 23, 2006 9:11 pm
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!
Posted: Tue Jun 27, 2006 7:54 am
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.
Posted: Tue Jun 27, 2006 8:14 am
by feyd
I'd personally go with separate databases in this case. Why? It's more modular and categorized.
Posted: Tue Jun 27, 2006 1:34 pm
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...
Posted: Tue Jun 27, 2006 1:41 pm
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

.