Page 1 of 1

Updating mutliple databases

Posted: Sat Dec 06, 2008 8:39 am
by EPX
Hi,

I've tried googling this but i not 100% sure what i should be asking.

i have developed a system which i want to rollout. but instead of running everyone in one big mysql database, i want to create a new database for each user.

is there a way when we do an update on the sandbox system to easily update all the other databases via one script?

Thanks

Re: Updating mutliple databases

Posted: Sat Dec 06, 2008 2:10 pm
by califdon
EPX wrote:Hi,

I've tried googling this but i not 100% sure what i should be asking.

i have developed a system which i want to rollout. but instead of running everyone in one big mysql database, i want to create a new database for each user.

is there a way when we do an update on the sandbox system to easily update all the other databases via one script?

Thanks
I assume you would be doing this for security control? If each user has access to only one database, when would you need to update all databases?

In any case, you will need to connect to each database individually in order to update it. That's why this doesn't sound to me like a good architecture, if you have a need to do updates across all databases.

Re: Updating mutliple databases

Posted: Sat Dec 06, 2008 5:39 pm
by EPX
yes it is mainly for security. i like the idea that everyone has a seperate database for there information.

the reason to update all databases would e if we want to rollout a new feature or general update etc.

i was hoping there would be some sort of feature which allowed for a master db which all other db could copy. but connecting to each one and alter it might be the only option then.

Re: Updating mutliple databases

Posted: Sat Dec 06, 2008 7:07 pm
by califdon
EPX wrote:yes it is mainly for security. i like the idea that everyone has a seperate database for there information.

the reason to update all databases would e if we want to rollout a new feature or general update etc.

i was hoping there would be some sort of feature which allowed for a master db which all other db could copy. but connecting to each one and alter it might be the only option then.
That could be a workable architecture if you expected a limited number of users, perhaps less than a few dozen, but in case you are designing this for thousands of users, I would advise against it. Every database requires some overhead in terms of storage, administration (backup, e.g.), and programming. When the overhead is incurred once for a large database, it is insignificant, but if you multiply it by tens of thousands, I think you are inviting a major headache. Will you ever have the need to query for data from all users? Even a simple list of users for internal purposes? Have you thought about how you could do that with some undetermined number of databases? There are many approaches to security that you should consider before splitting data into large numbers of small databases. Just my opinion.

Re: Updating mutliple databases

Posted: Sat Dec 06, 2008 7:33 pm
by Eran
Califdon is generally right, and you should consider what he says. The only reason I'd consider multiple databases is if you are planning to run each in isolation (separate machines).

Re: Updating mutliple databases

Posted: Sat Dec 06, 2008 9:19 pm
by Kieran Huggins
Only once have I ever seen a practical reason for thousands of distinct (yet structurally identical) databases, and they were optimizing some massive queries across hundreds of nodes in a beowulf... and even then it felt dirty ;-)

Don++

Re: Updating mutliple databases

Posted: Sun Dec 07, 2008 12:06 pm
by EPX
points taken and i think i will head towards the one database route.

origanally i liked the idea of seperate databases for each client, but i can see it getting to complicated and difficult to manage multiple db's

cheers for the help