I'm working on a pretty standard LAMP based web application. The server itself has a standard MySQL database and the application is written in PHP. The kicker is, we also use SQL Server databases with this application.
The current data setup is this:
- MySQL stores the user account information (Username, password, privileges, etc.) and a "connection identifier".
- The connection identifier links to another table that stores connection credentials for a SQL server database.
- When a user logs in, a connection is made to a remote SQL Server database (these are hosted independently of the web application and not in my control).
- Using that connection, the web application processes and outputs the data (only reads, no writes).
The application has evolved greatly and, while the organization and practices have improved, the design is still pretty messy and confusing. I struggle to implement any kind of framework or ORM because most are designed with one database in mind.
I have stitched together a framework that works very well for reports (where only the SQL Server connection is needed) but it doesn't go well with things like account interaction (where SQL server is used for validation of data that goes into MySQL).
The setup doesn't look like it will change so I was wondering if there was any advice on designing and maintaining a web application with two different database backends at the same time.
On a side note, storing the credentials for remote SQL Server databases on our MySQL database makes me nervous. I can only encrypt the passwords as I need them for connections and, as it currently stands, all the accounts used are admin accounts with full privileges. Not something you want getting out especially when these databases are opened up for internet connections. So any advice for that would be nice as well.