10

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?

Improve this question
4
  • 4
    Don't do comma delimited values. It will be something you have to parse through later. A normalized table structure will do fine just fine. Commented Feb 28, 2014 at 19:45
  • we used simple math to overcome this issue.... we set the enum values as, 1, 3, 5, 7, 9.... and if a message is for a single group, set the value of the message to one of the numbers, if there are a couple or few groups that can view the message, add the number of the groups, and set the value for that message as the sum Commented Feb 28, 2014 at 19:47
  • @pithhelmet Should have explained this in my post, but currently Users are 1, Supervisors 2, Admin 3 and ITOperator 4. The problem is what if the announcement is visible to Users, Admins and ITOperators, or if it is visible to everyone by Users, etc.? Commented Feb 28, 2014 at 19:49
  • Removed my instructional comment as it is no longer necessary. Instead I urge you to follow the JDB solution. Commented Feb 28, 2014 at 19:55

3 Answers 3

Reset to default
7

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

^^^^ this is exactly what I meant in my comment to the OP in my initial segment. +1 to you sir.
While I appreciate the advice in general, this does not answer the question at all. If I want to store Enum values, those values will not change, and ever worst, each value have an specific meaning at development time. So, if you make a table for those Enum values you will end using an Enum anyway to give meaning to each static table's row. For this specific question, the solution should be @xxbbcc 's one.
@David I don't think you understood the question. The question was how to associate an announcement with multiple roles in SQL. You can't use a flags annotation in SQL.
Dear JDB, please read the question again. Notice he is specific about using the Enum. He wants to use a simple Column to store the values, he knows he have multiple values (he mentioned comma possible delimited solution). Yes, he could make the many-to-many association but will have to write a lot of code with no reason or need. The xxbbcc's solution is more practical in this case. You don't need any special SQL annotation, just store the value of the variable, just have to use the boolean operator to compose/decompose it. Your proposal is correct in general, but this is very specific.
@DavidSilva-Barrera - I can see your point of view. Personally, I think the database should ideally stand on its own without much concern for client source code. I've been on several projects where an existing database needed to be preserved, but the client was entirely rewritten (e.g. desktop to web). Storing Enum flags in a column seems short-sighted to me... SQL queries become more complicated and difficult to understand, in my opinion, but I guess it all comes down to the individual project. I stand by my answer, but recognize there are other opinions.
5

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.

Improve this answer

2 Comments

This gets dangerously close to "magic numbers" in the database. Sure, you know what they mean if you have the C# source, but only if you have the C# source.
I don't see it as Magic Number, you are storing the unique int identifier of the enum value in the database, if this is wrong then so is storing the primary key of a table in another table as foreign key. If you don't have the source code or at least the documentation, why are you administrating that database in the first place? The referred article does not talk about databases at all, but magic number in the source code where it's a problem even having the source code.
0

Just do as Entity Framework would do and store them as ints.

Check this out to learn more... MSDN

Improve this answer

2 Comments

And why not have another table, that's what databases are for!
I like @xxbbcc 's idea but can't comment on it

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.