Shared entities across multiple databases

Question

I'm currently working on an application with a master database and individual user databases where all user input data resides. These individual databases are exactly the same. master connects the dots and links the users to their database.

Today, we're looking into sharing a specific user entity in the way that other users should be able to access the other.

Say we have the table comments. Each comment have a primary key that may or may not exist in other user databases. What I'm thinking is to have a relation tracking table in master that basically says "this user have access to this user's comments".

The conceptual problem I'm facing is the union part, where multiple users have access to one users comments. Given that database names are pattern based user_id.comments. I just don't see how it would be possible to union all comments across multiple databases.

Scenario;

master.comments_relation
user_1 user_2
user_1 user_3
user_2 user_1
user_1.comments
1 "comment from user 1"
2 "another comment from user 1"
user_2.comments
1 "comment from user 2"
2 "another comment from user 2"
user_3.comments
1 "comment from user 3"
2 "another comment from user 3"
What I want to be able to output now as "user_1"
1 "comment from user 1"
1 "comment from user 2"
2 "another comment from user 1"
2 "another comment from user 2"

This is a totally new thing for me and I have no idea on how this is supposed to work, and the primary keys are conflicting. Is it better to move all comments to master database, and skip the multi-database approach, having shared entities in the same database?

• jkavalik: Thats what you get when you use data (user_id) as metadata (table/database name). The thing you may want is often called "multi-tenant" - you mostly use one database and one set of tables, and separate the users by some identifiers - thats for the case when you have some small number of users (clients). If those are just people registering an account, then thats not even multi-tenant at all.

Answer 1

One instance of MySQL can have one or more databases. Each database can have one or more tables. It sounds like the info belongs "together", so one database makes sense. It is almost always a "bad" idea to have "one database (or table) per user/product/company/item/etc".

This forum is littered with discussions of why "one per" is bad.