[ciapug] Best Practice

[Claus]

On 1/22/2005 9:47 AM, Dave J. Hala Jr. wrote:
> Ok... So...I've got this client intake system. Its written in PHP and it
> uses a mysql back end.  The information that is stored is demographic
> data on individuals and households.
> 
> I have 9 agencies that use it. Each runs agency has their own website
> and database. (they are all identical)  What I would like to do is mine
> data across all 9 agencies.  At the moment, its kinda a pain to connect,
> and query each individual database and then combine all the information.
> 
> For political reasons, I can't have all the agencies use one database.
> What I was considering is having a single "Master Database" that any
> inserts or updates done any of the 9 individuals db's would be written
> to. For example site1 does an update on the site1 db and then it does an
> update on the master db.
> 
> This method is kinda like the mysql replication. I was initially looking
> for a way to replicate all the info from one db into the master but no
> luck.  I was thinking about trying to get the query info from the mysql
> *.bin files and using it to fill the a  "master db" ( not to be confused
> with the replication master)
> 
> 
> Anyone have any thoughts? Ideas?
> 
> 
> :) Dave
> 

I'd probably consider doing an unload of new data every night (or once a 
week/month depending on your situation) and load it into the 
master/mining db.  You should be able to schedule it via cron or 
something.  This reduces the load a bit from the actual transaction and 
often mining data does not have to be up to the minute anyway.

Another alternative if you use some kind of pre-determined mining 
queries that access each database individually and pull it together. 
For example you create a web page with php that displays the mining 
data.  In that php script you might be able to pull the data from the 
various sources.

   Claus