How do I handle multiple datasources?

Question

I am working on a web application (PHP + Doctrine + MySQL) to sell. The problem is that there is information that the clients will need from my central data source and they will have information that they don't want me to see (financial and such).

My Question, what is the best way to get that information to the customer application?

My Initial Thoughts: The application I am creating is built with Doctrine. I think I can create a connection to two databases by using something like (let me know if you know exactly how to do it):

$this->setTableName('host.dbname.tablename');

The problem with this is that I would have to open up the MySQL port, which a hosting company may not do for me nor may it be safe. Let me know if this is not so.

So I thought about setting up a php-cron job that will download the new data every day (daily would be sufficient). This would require some sort of php page on my data source to output the files. The problem with this is figuring out what information the client app has and updating it.

Edit: I just read about Federated Tables. This may be another solution if I can get the host to open port 3306.

Thanks

Answer 1

Create a view at the finance database to the table in question, then create a federated table at your web source database based on that view. If all you're going to do is read, then your web user only needs SELECT privileges.