I am developing a chat application as my the first project. I have never had experience of establishing a connection to the remote database server before so I don't really understand who should have access to that DB.
I have a user part (with GUI), server part deployed at the remote machine (to send messages from one user to another) and MySql server deployed at the same machine as a server part.
After user logs in to the system, user data stored in a database should be sent to the user part. My question is if the database should be connected to the user part directly to provide that data or the server part should only have access to the database and in order to get data from it user part should send requests to the server part?
Best Answer
Direct connection of the end user part to the database part is not the recommendable way, because if you allow such a connection:
For all these reasons, it's much better to let the client parts communicate with the server part. Only the server part would then connect to the database. The database should be configured to accept only connections from the server and isolated form any other trafic.
The server part, which is under your control, would then manage the access to the data, and enforce that a user will get access only to relevant data. As you control the server part, you may also assume that the database requests are legitimate and let the server ensure consistency. Whenever the server becomes a bottleneck, you may well run additional server instances connecting to the same database engine (3 tier approach)