I'm creating a site with Craft that needs to communicate with an existing database, which is separate from the database Craft will be installed on. My client is already using an existing online database to manage a bunch of their internal work, but I need to fetch some of that data for public display on the site. I don't need any relational queries between the two databases, so it's fine that they're separate.
My plan is to create a plugin that will create a new Record. Because BaseRecord extends Yii's CActiveRecord class, I should be able to override the getDbConnection() method to return a new CDbConnection object, which will connect to the external database.
So my first question is: Will that work, in theory? Or will Craft try to use my site's table prefix in this external database (which wouldn't work)?
My second concern is that Craft will associate the table returned by getTableName() in my new record as a table specific to this plugin, and will delete that table if the plugin ever gets uninstalled. Obviously since I'm dealing with an external database, I don't want that to happen. Is there any way around that?
I just came across this question: Query table in a different database, and the solution there seems like it's a simpler option for me. I am still interested in learning the answers to my above questions though.