Proposal - Use Enum Tables for User Permissions

I was looking at the 2 proposals for the future DB schema we are going to have and noticed that both these schema proposals use columns to represent user permissions.


IsAbleToAsk (Boolean default TRUE)
IsAbleToAnswer (Boolean default TRUE)
IsAbleToComment (Boolean default FALSE)
IsAbleToSuggestEdits (Boolean default FALSE)
IsAbleToUpvote (Boolean default FALSE)
IsModerator (Boolean default FALSE)

With this kind of approach every time there is a new feature needed there will be a need to add another column and change the database schema.
Like for instance being to edit only blog posts.

So as a solution I suggest add another table which will hold each permission and its type.

TABLE UsersPermissions
UsersId (bigserial)
CommunitiesId (bigserial)
PermissionTypesId (bigserial)

In a similar approach shorten the UsersSuspensions Table

TABLE UserSuspensions
UsersId (bigserial)
CommunitiesId (bigserial)
BanStartDate (date)
BanEndDate (date NULL)
BanTypesId (bigserial)

For reference:


3 Likes

I think that putting the specific privileges and suspensions into a separate table makes sense while IsSuspended can stay on the Users table.

Did you mean to say a reference table instead of an Enum?

1 Like

IsSuspended does make sense.

And yes reference table would be more correct

1 Like

I agree with this proposal - I was going to make the same suggestion.

It should also be explicitly defined that the lack of a permission indicates permission is denied for the action.