designing three dimensional relationships with ORM (TypeORM)

Question

I tried to create a database schema with users, groups, documents and permissions.

• users can join multiple groups
• groups can have multiple users
• users can have permissions for documents
• groups can have permissions for documents
• permissions can be anything, not for documents only

I tried to create a small graphic for that

I started designing the entities

User

@Entity('User')
export class UserEntity {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @ManyToMany((type: any) => GroupEntity, (group: GroupEntity) => group.users)
  @JoinTable()
  groups: GroupEntity[];

  @ManyToMany((type: any) => DocumentEntity, (document: DocumentEntity) => document.users)
  @JoinTable()
  documents: DocumentEntity[];
}

Group

@Entity('Group')
export class GroupEntity {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @ManyToMany((type: any) => UserEntity, (user: UserEntity) => user.groups)
  users: UserEntity[];

  @ManyToMany((type: any) => DocumentEntity, (document: DocumentEntity) => document.groups)
  @JoinTable()
  documents: DocumentEntity[];
}

Document

@Entity('Document')
export class DocumentEntity {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @ManyToMany((type: any) => UserEntity, (user: UserEntity) => user.documents)
  users: UserEntity[];

  @ManyToMany((type: any) => GroupEntity, (group: GroupEntity) => group.documents)
  groups: GroupEntity[];
}

When it comes to the persmissions for documents you will see that the relation exists between 3 tables, users/groups, documents and permissions.

1. I use TypeORM for REST APIs (NestJs) and I'm not sure if a permission is an Entity. Due to the fact I'm designing REST endpoints this permission entity would be a shared entity along multiple endpoints.

2. How do I have to extend my entities for these permissions?

... and maybe you might come up with a better database design :)

Answer 1

In this case it is recomended to handle it manually, create an entity class CrossGroupDocumentPermissionEntity and add a ManyToOne for each relation and on the other side on the Document, Group, User add a OneToMany Relation here is an example of the use in ManyToMany with custom fields

Edit:

User

@Entity('User')
export class UserEntity {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @ManyToMany((type: any) => GroupEntity, (group: GroupEntity) => group.users)
  @JoinTable()
  groups: GroupEntity[];

  @OneToMany((type: any) => CrossUserDocumentPermissionEntity, (documentPermission: CrossUserDocumentPermissionEntity) => documentPermission.user)
  documentPermissions: CrossUserDocumentPermissionEntity[];
}

Group

@Entity('Group')
export class GroupEntity {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @ManyToMany((type: any) => UserEntity, (user: UserEntity) => user.groups)
  users: UserEntity[];

  @OneToMany((type: any) => CrossGroupDocumentPermissionEntity, (documentPermission: CrossGroupDocumentPermissionEntity) => documentPermission.group)
  documentPermissions: CrossGroupDocumentPermissionEntity[];
}

Permission

@Entity('Permission')
export class PermissionEntity {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @OneToMany((type: any) => CrossUserDocumentPermissionEntity, (userDocument: CrossUserDocumentPermissionEntity) => userDocument.permission)
  usersDocuments: CrossUserDocumentPermissionEntity[];

  @OneToMany((type: any) => CrossGroupDocumentPermissionEntity, (groupDocument: CrossUserDocumentPermissionEntity) => groupDocument.permission)
  GroupDocuments: CrossGroupDocumentPermissionEntity[];
}

Document

@Entity('Document')
export class DocumentEntity {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @OneToMany((type: any) => CrossUserDocumentPermissionEntity, (userPermission: CrossUserDocumentPermissionEntity) => userPermission.document)
  usersPermissions: CrossUserDocumentPermissionEntity[];

  @OneToMany((type: any) => CrossGroupDocumentPermissionEntity, (groupPermission: CrossUserDocumentPermissionEntity) => groupPermission.document)
  GroupPermissions: CrossGroupDocumentPermissionEntity[];
}

Cross_User_Document_Permission

@Entity('Cross_User_Document_Permission')
export class CrossUserDocumentPermissionEntity {
  @PrimaryColumn()
  userId: string;

  @PrimaryColumn()
  permissionId: string;

  @PrimaryColumn()
  documentId: string;

  @ManyToOne((type: any) => UserEntity, (user: UserEntity) => user.documentPermission)
  user: UserEntity;

  @ManyToOne((type: any) => DocumentEntity, (document: DocumentEntity) => document.userPermission)
  document: DocumentEntity;

  @ManyToOne((type: any) => PermissionEntity, (permission: PermissionEntity) => permission.userDocument)
  permission: PermissionEntity;
}