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.

Tags:

C#

Sql

Enums