relations between tables in multiple databases

Question

I am building a complex plataform, and I want to have a dedicated database for each "module" of it. But I need to have a "master" database which has some common tables like the users table. I need to be able to have foreign keys between tables in multiple databases. (like the user_id)

I know I can have cross database relations if the databases are in the same server which they are right now, but I dont want to be limited by that, since if the application grows alot i might need to separate the databases into multiple servers.

So, I am looking all my options, before taking my decision.

Whats your toughts about this?

I am using mysql.

Answer 1

You can do this using the FEDERATED storage engine. What you do is you create a local table utilizing the FEDERATED engine, which in turn will connect to the remote system to resolve queries. I have no idea about the performance of this, though.

---

A better approach may be to rethink the application data model. I can understand why you want to have separate databases for each module in a complex system, but it gets hard when you want to share some of the table data. It will be even harder with maintainance if you introduce some restrictions on the data enforced in one module but not in another.

I think the solution is to separate concerns about the data. Let each module have its own database, but don't let the modules query others data! If a module needs data about a user, let it fetch it through the user module.