Databases: Save users and admins in the same table (pros/cons)?

Question

I'm interested in your opinion: at the moment I've only one user-table in my database but I think about adding a new one to separate public accounts from the admin accounts.

Pro: I don't have to check the "roles"-table for validating the user who tries to log in.

Con: Admin accounts cannot be part of the community and are just for the backend. Thats too much redundance if you promote somebody to an moderator: he cannot write posts with his public account.

My current solution to check, if a user is a team-mate (pseudo-code):

$needed_role = "admin";
if ($User->is_in_team($user_id)) { // SELECT id FROM user WHERE team=1 AND user_id=$user_id
    $roles = $User->getRoles($user_id);
    if (in_array($needed_role, $roles)) {
        // login...
    }
}

That's an easy example. The Roles are divided in rights like "blog_post_write", "blog_post_delete", ...

The solution I'm currently isn't perfect, so please help me to pimp my database! :)

Answer 1

Can you not simply have one users table with is_admin and is_moderator boolean variables. Then when you want to look up who is in the teams, you only have to look at one table. I wouldn't think about separating them unless you actually want them to be a different kind of user and not just a user with admin or moderator privileges.

Answer 2

Is any special reason to have separate tables ? Maybe more extra fields ? Having to check other table as a secutiy measure ?

..+---------+-----------+-----------+-------------+
..|             User                              |
..+---------+-----------+-----------+-------------+
..| UserID  | UserAlias | UserPwd   | UserIsAdmin |
..+---------+-----------+-----------+-------------+
..| 87      | johndoe   | sdsd<ds33 | false       |
..+---------+-----------+-----------+-------------+
..| 88      | janesmith | sd456656h | true        |
..+---------+-----------+-----------+-------------+
..| 89      | annethmps | s34ddd545 | true        |
..+---------+-----------+-----------+-------------+
..| 90      | mikekane  | s34ddd545 | false       |
..+---------+-----------+-----------+-------------+

..+---------+-----------+-----------+
..|             Admin               |
..+---------+-----------+-----------+
..| UserID  | Extra1    | Extra2    |
..+---------+-----------+-----------+
..| 88      | sdfsds3   | s2323h    |
..+---------+-----------+-----------+
..| 89      | sdsds2    | s3ghgh5   |
..+---------+-----------+-----------+

Cheers.