I'm attempting to build a single application that consists of multiple databases each with very different requirements.
Server1: Files (.txt .docx .png) as attachments. Server2: Calendar events. Server3: User data. Server4: Inventory.
Sure, I could all all of these to a single database. However, I'm sure a database used for storing files requires completely different optimisation to run efficiently in comparison to one that stores largely string values. So I'm looking to separate the load into multiple databases for that reason, and modularity/security. Having all the data in one database, if compromised, gives access to everything from a single hack.
The question is a fairly simple one. How do I link the databases across multiple servers so the data can be linked/queried e.g.
Example: User "Cathie" will have an entry in the Server3 containing her name and emails, she may also have files attached to her account (Server1), or own a company device (Server4). I would like to be able to search all 3 servers using a single search query, can this be done in MySQL relationships?
Happy to hear some out of the box ideas also.