Best way to store multiple enums in database

Question

I have an Enum called RoleEnum with four values User (1), Supervisor (2), Admin (3), and ITOperator (4). I have an Announcement table that obviously stores Announcement data (title, text, start and end date). However, I'd like to be able to add a new column called Roles that defines which users in which roles can see the announcement.

Normally when storing Enums in SQL I just use the smallint data type, however that isn't going to work in this situation because an announcement can be seen by more than one role.

What I was considering doing was having the Roles column defined as a varchar(x) and comma delimiting them when they were stored. However, I don't think/know if that is the best route to take. I don't want to create a new table to hold a one-to-many relationship.

Any ideas?

Answer 1

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