You have a website with a user base. You want to allow some users to do more than others. How would you go about designing the DB to support that?
Would it be a horrible idea to use a table like:
users(id, name ... can_add_comments, can_edit_comments, can_add_items ...)
You could define roles that can be assigned to the users. Then define operations allowed for each role. It can get fancier from here, e.g. group or account level roles that all users within that group automatically receive the privileges to perform group role operations.
create table permission(id int primary key, desc varchar(64) not null)
create table user_permission(pid int primary key, uid int, permissionid int)
Foreign keys left to the reader.
Or, read the source of Drupal to see a live example.
The point here is that you don't want to tie yourself to a fixed set of permissions. You might invent a new one next week.
I'd suggest a table for users, a table for roles, and a table for relating users to roles.
You could have a table of authorizable activities and then a table relating roles to authorized activities, but you could also get away with handling the roles within the application and/or granting/denying specific activities in the roles table.
I imagine for an application with tons of individual permissoins would call for a table relating roles to authorized activities (you wouldn't want to check off 30+ permissions for each individual user).
Yes. Every time you add a new capability, you have to add a new column and assign a default value to each user already in the system. How about using a little normalization to separate the users from the capabilities?