Shared Content - Slight Change of Plans

So, initially it was intended to have a single table called "shared_content" to store information regarding the shared content, which would have the following fields:-

(content_id, user_id), both of which form the primary keys.

In the current implementation, when a particular content is shared with a group, we populate the database with all the users, who are in that group. But this would make the modification to any particular group difficult, as in that case corresponding changes to the "shared_content" table would also be needed.

So I think rather than populating the "shared_content" table with multiple rows, when content is shared with a group, it would be better to make another table "shared_content_group", with the following structure:-

(content_id, group_name), both of which form the primary keys.

In this way the group can be modified by the user. In case a group is deleted by the user, then we need to make sure that the corresponding entry is also removed from the "shared_content_group" table, otherwise if the user makes the group with the same name he deleted then this would result in problems, due to wrong entries in the database.

Now we have two scenarios:-

1. Find the users, with whom the author shared his content :- This is needed for making the UI of content shared by the author. I plan to do this by first querying the "shared_content" table, which would give the list of users, with whom the content is shared, then query the "shared_content_group" table and find the groups with which the content is shared. Finally, use this information to extract the users from the "group_users" table and make the complete list of users with whom the data is shared.

2. Find the content shared with any register user :- Getting this information from the "shared_content" table is easy, but getting it from the "shared_content_group" is a bit difficult. For this we need to first extract all the groups of which the user is a part of. Then find the content_id's (if any) associated with these groups from the "shared_content_group" table.

I think this would do the trick, I'll try to come up with the implementation of this in the next few days.