Best way to store multiple enums in database
If you care about maintainability, I'd stick with third normal form as much as possible.
Roles
RoleID RoleName
1 User
2 Supervisor
3 Admin
4 ITOperator
Announcements
AnnouncementID Title ...
1 Foo ...
2 Bar ...
AnnouncementsVisibility
AnnouncementID RoleID
1 1
1 2
2 2
2 3
2 4
This is one possible solution - not guaranteed to be the best but it doesn't require a new table.
You can add the [Flags]
attribute on your enum - this makes the enum a bit field where individual enum values can be masked together. Your enum would then look like this:
[Flags]
public enum RoleEnum : long
{
User = 1,
Supervisor = 2,
Admin = 4,
ITOperator = 8
}
You can use the '|' (bitwise OR
) operator to mask multiple roles together in a single 64-bit integer, which you can store in the database in an integer field (a bigint
).
RoleEnum userRoles = RoleEnum.User | RoleEnum.Admin;
If you don't need 64 possible roles, you can drop down to using an int
instead - that gives you 32 possible distinct roles.