I'm beginning work on a web application that will have several distinct user types, example being Registered User, Site Admin, User Admin..etc. Users may have more than 1 role.
In previous projects, I've been able to manage different user schemas with 3 tables: User, UserRole, User_UserRole. With this approach, it was relatively easy to manage the M-M relationship between User and UserRole (in User_UserRole table).
However, I don't think this approach will work so nicely in this project for the following reason. I need to be able to track who (site admin or user admin) approves each user.
I haven't been able to come up with any elegant solution for this, and am looking for some suggestions. The simpler the better.
Just include a granted_by column in the User_UserRole table that points to the User table.