I've a simple many to many relationship with TypeORM
@Entity({ name: 'users' })
export class User {
@PrimaryColumn()
id: string;
@Column()
email: string;
@Column()
password: string;
@ManyToMany((type) => Organization, (organization) => organization.users)
@JoinTable({
name: 'user_organizations',
joinColumn: {
name: 'user_id',
referencedColumnName: 'id',
},
inverseJoinColumn: {
name: 'organization_id',
referencedColumnName: 'id',
},
})
organizations: Organization[];
@Entity({ name: 'organizations' })
export class Organization {
@PrimaryColumn()
id: string;
@Column()
name: string;
@ManyToMany((type) => User, (user) => user.organizations)
users: User[];
}
My goal is to create a relationship which not only defines which user relates to which organisation it should also contains information in which role the user is related to a organisation. My idea was to extend the relation table with an extra role column for that.
create table user_organizations(
user_id varchar(64) not null,
organization_id varchar(64) not null,
role varchar(64) not null,
foreign key (user_id) references users(id),
foreign key (organization_id) references organizations(id),
);
My question is how to store the role in the database. Currently I'm doing something like this.
let user = new User();
let organization = new Organization();
organization.name = name;
organization.users = [user];
organization = await this.organizationRepository.save(organization);
How can I fill also the role column via TypeORM?
The best approach to this kind of problem is to create a separate table solely for Role and then refer to this table in the user_organizations.
But think of this scenario - what if a user doesn't have just one role? It can and does happen. That being said, my suggestion would be handling the role outside of the user_organisations table. Since the table is M2M, the primary key will be a composite of User.ID and Organisation.ID. My suggestion would be:
Roles and UserOrganisations