Using an enum on one side of a many to many relationship?

by

If I have an enum for role and I have a table of suppliers, how can I allow a supplier to have multiple roles?

I have considered a hasMany relationship from supplier to a supplier_role table, but the supplier_role table is then just full of repeated enums in each row:

id | role     | supplier_id
1  | producer | 1
2  | producer | 2
3  | producer | 3
4  | producer | 4
5  | producer | 5
6  | reseller | 1

I’ve considered removing the enum and going for a traditional pivot, with another table defining the roles. For example, the tables would look like:

supplier | supplier_role | role

Then instead of the enum, each enum value would be a row in the role table. Then linked via a manyToMany.

But ideally, I want to keep the enum, it’s used elsewhere in the application. What is the standard approach here (maintaining the use of the enum)?

Leave a Comment