I am developing a user hierarchy system for a web site and I am having trouble deciding what the best structure would be. I was originally deciding between two different methods. The first was using a "UserType" field in the users table that simply specified what type of user it was. The second was having a separate table for each type of user that would contain "UserID"s from the users table. I ended up going with the second method because different user types would have extra data along with that data in the users table. The problem is that I am having trouble with the queries.
When trying to get a user's information, since I don't know what his user type is from the users table, I need to check all of the other tables for an entry. The best way I can come up with to do this is like so:
SELECT *
FROM users u
LEFT JOIN user_admins ua ON ua.UserID = u.UserID
LEFT JOIN user_staff us ON us.UserID = u.UserID
WHERE u.UserID =1
The problem with this is that it will return null values for every field in the tables where the user doesn't exist. I would, for example, select UserID from user_admins as "isAdmin" and then check that value in php (or even in MySQL if it is more efficient) but that seems like it might become pretty time consuming. Is there a better way to do this that would be more efficient?
Also, when checking user access rights, if I wanted to check if a user is a staff OR an admin, the query looks a little ridiculous:
SELECT IF(
(SELECT UserID FROM user_admins ua WHERE ua.UserID = u.UserID),
true,
IF(
(SELECT UserID FROM user_staff us WHERE us.UserID = u.UserID),
true,
false
)
) as access FROM
users u
WHERE
u.UserID = 1
There has to be a better way than this to do it, right?
I'm not too far into this to change the database structure, so if someone can give me a better way to structure this that would make the queries less difficult, that would be helpful too.
Thanks for any help.
