I am trying to figure out the logic around setting up and segmenting a database with a multi-user and multi-group functionality. Quick explanation... Pretend there is an online file sharing application (it's not, just an example). I need to have users, and groups, and permissions for each user. If a user is an administrator, they can see all the files for other users in their group. If they are not an administrator, they can only see their own files. There is an unlimited numbers of users that can be added to each group. Some logic can exist on the application level, but what is the proper way to segment and organize this type of database while retaining data integrity? So far, I would start with. Users: user_id user_level other_user_fields Groups: group_id group_name other_group_fields User_groups: ug_id user_id group_id Files: file_id file_name other_file_fields Now, what is the best way to attach the files to the user/groups with the ability to segment based on the user_level? Would I start with: Files_Ug: fu_id file_id ug_id Problem here is that user_permissions wouldn't matter. I could instead do: Files_Ug: fu_id file_id user_id Problem here is that group wouldn't matter. Any thoughts or references to multi-user, multi-group application programming.
Hi jestep, I would simply go with the model you started with and add one column - user_id into table files. That would represent the owner of the file: Files: file_id user_id file_name other_file_fields Then you have all the dependencies you're talking about, because every user is in user_groups and files. And using JOIN you can query the DB and get all the records you need. Hope it helps