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?
