Database structure and query building-CodePudding

Please advise how to accomplish this task.

There is an entity - the user. He can create a group in which there will be other users with certain rights.

Here I'm interested in how to correctly cost requests to get groups for the user and guest.

I assume that the group will have corresponding fields for which I will make a selection, but I don’t really understand how to properly store the role of the guest in this group. I have one idea, but I'm not sure if it's ok.

interface Group {
  id: string;
  ownerId: string;
  guestsId: string[];
  // or my idea :)
  guests: Array<{
    guestId: string;
    role: string;
  }>
}

Perhaps you need to create another table for this? I am using relational db

Same way. Question number 2. Each user has a photo, and each group has pictures.

I need to restrict access to pictures (getting will be through api with middleware, not static) for different users.

Pictures in a group can only be viewed by its members.
A user's photo can be seen if you are in the same group with him.

I don't have many ideas here. I see the problem that in order to get a picture, you need to check which groups the user who requested the picture belongs to.

All that came to mind is when generating a jwt token, get a list of user groups and add them to this token. But I'm not sure.

I would appreciate your ideas and advice.

CodePudding user response：

From a database standpoint, I see tables:

User table

Userid (PK)
UserName
UserPhoto

Group table

Groupid (PK)
GroupName

Rights table

Rightid (PK)
RightName

Picture

Pictureid (PK)
PictureLocation

Link tables

Group_has_rights

Groupid (FK)
Rightid (FK)

Group_has_users

Groupid (FK)
Userid (FK)

Groups_has_Pictures

Groupid (FK)
Pictureid (FK)

Using the Groups_has_SOMETHING tables, you establish many-to-many relations. This way, users, rights and pictures can be associated to many groups.

It gives you complete flexibility for match current and future requirements and combinations.

With link tables, this being a relational database, you can use JOIN statements to extract any list of required items. No need to iterate through lists in your code, the database does it all.

For your guest users, I do not understand your requirement. Define what rights a guest user should have and associate it to a group that has those rights in Groups_has_Rights.

Your application code can associate a guest1, guest2, ... user that is created on the spot, with a link to the appropriate group(s).

For your pictures, you get the list of groups for the current user in Groups_has_User. Then you get a list of pictures to display through Groups_has_Pictures, for the groups you listed above. Use JOIN in your SQL.

For the photos, you get the list of groups for the current user in Groups_has_User. From that list, you get a list of all other users that are in the same groups, again from Groups_has_User. Then from the users list, you select the UserPhoto. Again, use JOIN in your SQL.

Obviously this only shows the basic structure of tables and relations. Each table will contain more information, I just demonstrated the squeleton.