Updating mutliple 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
EPX
Forum Newbie
Posts: 19
Joined: Fri Nov 21, 2008 3:22 am
Location: Stafford, UK

Updating mutliple databases

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Updating mutliple databases

Post 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.
EPX
Forum Newbie
Posts: 19
Joined: Fri Nov 21, 2008 3:22 am
Location: Stafford, UK

Re: Updating mutliple databases

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Updating mutliple databases

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Updating mutliple databases

Post 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).
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: Updating mutliple databases

Post 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++
EPX
Forum Newbie
Posts: 19
Joined: Fri Nov 21, 2008 3:22 am
Location: Stafford, UK

Re: Updating mutliple databases

Post 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
Post Reply