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
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.
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
master database, and skip the multi-database approach, having shared entities in the same database?